Thursday, July 10, 2025
Alternative Way
  • Home
  • Latest
    • Latest
  • News
  • World Tech
  • World Gaming
  • Minecraft
  • Guides
  • Contact Us
  • About The Team
    • Privacy Policy
    • Terms of Use
No Result
View All Result
  • Home
  • Latest
    • Latest
  • News
  • World Tech
  • World Gaming
  • Minecraft
  • Guides
  • Contact Us
  • About The Team
    • Privacy Policy
    • Terms of Use
No Result
View All Result
Alternative Way
No Result
View All Result

Excel VBA: If Cell Contains Value Then

Gordon James by Gordon James
October 3, 2021
in Latest
0 0
0
Home Latest

In this article we will see how to automate the beginning of a certain action when a cell in a spreadsheet contains a certain value. There are three ways to do it: InStr, please and find.

A tool is a function that displays the position of the searched text in another text. In this example we use the Instr() function to check if a cell contains the word apple. We use the Do-loop to scroll through all recordings in column A and check each recording.

alt= sample data for device data Width-original width=650 Height-original height=222 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/Excel-VBA-If-Cell-Contains-Value-Then.png data-ez= />

alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />

Sub Use_Instr()
R = 1 1 1
‘Run until the last string
Do Until Range(A & R) =
‘Check each cell to see if it contains an ‘apple’, then…
…location Contains an apple in column B
If InStr(1, Range(A&R), Apple),
Range(B&R) = Contains an apple
End If
R = R + 1 1
Loop
End Sub

After executing the above code, we get the following results:

alt=A first time data with instr – some data was found but not all data – original width=682 data – original height=192 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/1604106089_270_Excel-VBA-If-Cell-Contains-Value-Then.png data-ez= />.

alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />

The reason we couldn’t catch the apples in row 5 is because they have a capital A. To make the InStr case insensitive, we can use vbTextCompare as follows

Sub Use_Instr_ignoreLetterCase()
R = 1 1 1 1
‘Walk to last string
Do Until Range(A & R) =
‘Check each cell to see if it contains an ‘apple’, then….
…place Contains an apple in column B
, if InStr(1, Range(A&R), apple, vbTextCompare) then
Range(B&R) = Contains an apple
End I
R = R + 1
Loop
End Sub

We have now correctly identified the apple in column 5.

alt=end of device passage for both cases data-orig-width=760 data-orig-height=201 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/1604106090_370_Excel-VBA-If-Cell-Contains-Value-Then.png data-ez= />

alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />

We can use Like instead of InStr as shown below. Note that we use the Text Comparison option at the top of the module to ignore the sensitivity of the cases when searching for words/letters. Also note the asterisk *, which we place before and after the search term. The following code gives the same results as the previous sample.

Option Compare text
Sub Use_Instr()
R = 1 1 1 1
‘Walk to last row
Doe Tot Range(A&R) =
‘Check each cell to see if it contains an ‘apple’, then…
…place Contains apple in column B
If Range (A&R) If *Appel* Then
Range (B&R) = Contains apple
End If
R = R + 1
Loop
End Sub

Using the search method can be very useful if you don’t have to worry about building cyclic logic on specific cells. The direction of the integrated cyclic movement of the search function can even be reversed, and not only forwards. I will give a very simple example of using Find here. More information can be found in this article: https://software-solutions-online.com/excel-vba-find-find-value-range-cells-vba/.

Sub Use_Find()
‘specifies the last row of column A
lastRow = Cells(Rows.Count, A).End(xlUp).Row
set searchRange = Range(A1:A & lastRow)
set searchResult = searchRange.Find(apple, MatchCase:=False, Lookat:=xlPart)
‘stores the address of the first search result, because the search does not start again until all matches are found.
firstCellAddress = SearchResult.address
‘If the search result is nothing = Error, do the following
If the search result is nothing = Error, do the following

Enter the number
Location Contains an apple to the right of the cell with the search result.
searchResult.offset(0, 1) = Contains Apple
‘Keep search for the next match (default direction is down)
Set searchResult = searchRange.FindNext(searchResult)
‘Keep looping until the search result is the first one we found
‘ (until we loop through the results we already found)
Loop during firstCellAddress <> searchResult.Address

End If

Last sub-heading

In this article we have shown three methods to check whether a cell contains a certain value. Finally, it is important not to forget the sensitivity of the register, as it is one of the most common sources of errors.

Related Tags:

vba if cell contains any text,vba if cell contains number,if cell.value = then vba,istext in vba,if cell contains text then msgbox,excel vba if range contains specific text,run macro if cell value equals,excel vba if formula in cell,excel vba if cell is blank then,excel vba if cells like,vba check if cell is equal to string,vba nested if then else in a for next loop,check if a cell contains a substring vba,if active cell contains text then vba,if cell in range contains text then vba,excel macro contains,excel if contains partial text,if cell is not blank,cell a20 display an orange background,excel check if cell is empty,vlookup contains,you want to add a column to the pivot table,excel vba if then multiple statements,vba if 2 criteria,double conditional vba,vba logical operators,excel vba check if two conditions are true,if cell contains specific text then return value in another cell vba,if range( ).value = then,excel vba if statement based on cell value,excel vba if cell contains any text,excel vba if cell contains partial text,excel vba if cell contains specific text then delete row,excel vba if cell contains number,vba if column contains value then

Total
0
Shares
Share 0
Tweet 0
Pin it 0
Share 0
ShareTweet
Gordon James

Gordon James

Next Post
TreeSet in Java | Methods, Example

TreeSet in Java | Methods, Example

  • Trending
  • Comments
  • Latest
How To Get Free Internet On Android Without Service

How To Get Free Internet On Android Without Service

March 10, 2022
🥇 +4 Neo Geo Emulators for Android  List ▷ 2021

🥇 +4 Neo Geo Emulators for Android  List ▷ 2021

October 3, 2021

Fix: Notifications not working on Nova Launcher

October 3, 2021
How to Fix OpenVPN Connected but Not Changing IP Address

How to Fix OpenVPN Connected but Not Changing IP Address

October 3, 2021

Setting Up Directory Sync Between On-Premises Active Directory with Microsoft 365 Azure AD

0
🥇 DELETE ACCOUNT from PS4  ▷ Step by Step Guide ▷ 2020

🥇 DELETE ACCOUNT from PS4  ▷ Step by Step Guide ▷ 2020

0
🥇 PPTX File Extension  What is .Pptx and how to open them? ▷ 2020

🥇 PPTX File Extension  What is .Pptx and how to open them? ▷ 2020

0
🥇 Make a Crossword in Microsoft Word  Step by Step Guide ▷ 2020

🥇 Make a Crossword in Microsoft Word  Step by Step Guide ▷ 2020

0
AI in Gaming: Better Player Experiences and Optimised Development

AI in Gaming: Better Player Experiences and Optimised Development

July 8, 2025
Gaming Without Borders: How Localization Helps Casinos Reach Global Players

Gaming Without Borders: How Localization Helps Casinos Reach Global Players

July 8, 2025
Artificial Intelligence Can Anchor Tech Collaboration Between the US and Saudi Arabia

Artificial Intelligence Can Anchor Tech Collaboration Between the US and Saudi Arabia

July 8, 2025
kmilafrench: Discover the Influencer Redefining Fashion, Humor, and Community Engagement

kmilafrench: Discover the Influencer Redefining Fashion, Humor, and Community Engagement

July 7, 2025

There's always an alternative Way!
Find us at 4145 Zolynthian Street, Vylorthos, QP 78425
No Result
View All Result
  • Home
  • Latest
    • Latest
  • News
  • World Tech
  • World Gaming
  • Minecraft
  • Guides
  • Contact Us
  • About The Team
    • Privacy Policy
    • Terms of Use

© 2022 - Alternative Way

Welcome Back!

Login to your account below

Forgotten Password?

Retrieve your password

Please enter your username or email address to reset your password.

Log In
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent.
Cookie SettingsAccept All
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT
No Result
View All Result
  • Home
    • Home – Layout 1
    • Home – Layout 2
    • Home – Layout 3
    • Home – Layout 4
    • Home – Layout 5
  • Travel News

© 2025 JNews - Premium WordPress news & magazine theme by Jegtheme.