VBA Find Range in Excel will help you while automating a task to find a cell with specific string or value in a range. You can specify the different options while looking for a string. It can be exact match, search direction, search after, etc.
VBA to Find Value in a Range – Syntax
Here is the syntax to find a string or value in a range. Find method will return the range of the matched cell.
Range(“YourRange”).Find(What,
[After], [LookIn], [LookAt], [SearchOrder], [SearchDirection As XlSearchDirection = xlNext], [MatchCase], [MatchByte], [SearchFormat])Here,
What: String or a value to be searched.
After – (Optional): You can mention the search start cell, By defaults it will be the first cell in the given range.
LookIn – (Optional):The information which you want look in the given range. It can be xlValues, xlFormulas and xlComments.
SearchOrder – (Optional): We can tell how to search weather it is by rows or by columns by using xlByRows and xlByColumns.
SearchDirection – (Optional): You can mention the search direction – next or preivious by using xlNext and xlPrevious.
MatchCase – (Optional): You can specify if your search is case sensitive: True = Case Sensitive.
SearchFormat – (Optional): You can specify the Search format.VBA to Find Value in a Range – Example
Below is the Excel VBA Macro to find a string in specific range and return the matched range address.
Sub Find_Range() MsgBox Range("B2:D10").Find(What:="ab").Address End SubThis macro will search for the string “ab” from the first cell of the given range and returns matched range address.
VBA to Find Value in a Range – After
Below is the Excel VBA Macro to find a string in specific range and return the matched range address. The start cell must be in the specified range.
Sub Find_Range_After() MsgBox Range("B2:D10").Find(What:="ab", After:=Range("B3")).Address End SubThis macro will search for the string “ab” from “B3” and returns matched range address.
VBA to Find Value in a Range – MatchCase
Below is the Excel VBA Macro to find a string in specific range and return the matched range address. The start cell must be in the specified range. When we write MatchCase:=True that means find string is case sensitive.
Sub Find_Range_CaseSensitive() MsgBox Range("B2:D10").Find(What:="ab", After:=Range("B3")).Address, MatchCase:=True End SubThis macro will search for the string “ab” from “B3” and returns matched range address. As we mentioned MatchCase:=True, it will look for exact match only.
VBA to Find Value in a Range – Instructions
Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:
- Open an Excel Workbook from your start menu or type Excel in your run command
- Enter some data in any cells in range “B2:D10″ to test this macro.
- Press Alt+F11 to Open VBA Editor or you can go to Developer Tab from Excel Ribbon and click on the Visual Basic Command to launch the VBA Editor
- Insert a Module from Insert Menu of VBA
- Copy the above code (for copying a range using VBA) and Paste in the code window(VBA Editor)
- Save the file as Macro Enabled Workbook (i.e; .xlsm file format)
- Press ‘F5′ to run it or Keep Pressing ‘F8′ to debug the code line by line.
Now, it will the search for the exact match string “ab” from “B3” and returns matched range address.