VBA Find Range in Excel

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


VBA Find Method Excel Range Object

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 Sub

This 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 Sub

This 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 Sub

This 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:

  1. Open an Excel Workbook from your start menu or type Excel in your run command
  2. Enter some data in any cells in range “B2:D10″ to test this macro.
  3. 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
  4. Insert a Module from Insert Menu of VBA
  5. Copy the above code (for copying a range using VBA) and Paste in the code window(VBA Editor)
  6. Save the file as Macro Enabled Workbook (i.e; .xlsm file format)
  7. 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.

LIMITED TIME OFFER

Leave A Comment


Related pages


delete duplicate records in excelconstruction cost estimate template excelcopy excel sheet to another workbookhow to protect a column in excel 2007unhide excel 2010how to create workbook in excelhow to password protect an excel workbookexcel vba create new workbookhow can i unprotect an excel sheethow to consolidate in excelunhide all exceliferror formulavba instringvba xlsdeclaring arrays in vbamsgbox in macroexcel formula list 2007excel delete cellsshort key for insert row in excelvba code to open a filehow to detect duplicate in excelexcel pivotchartexcel auto fittext box vbavba example programsconsolidate data from multiple worksheets in a single worksheetexcel vba syntaxmacro code for excelsql pivot table tutorialisequal excelunprotect xls filehow to delete extra rows in excelexcel advanced macrosworksheets in vbadelete column exceleliminating duplicate rows in excelexcel formula for countifwhat does vlookup meansql sasxls macro programmingvlookup function excelcheck marks in excelvba filedialogvba write text filevba activate workbookhow to dedupe a list in excelexcel radio buttonsvisual basic excel 2007 tutorial pdfbreak loop in vbainterview questions in testing for freshersswitch vba excelhow to lock a sheet in excelexcel sorting rowsexcel multiple criteriaduplicate excel worksheetadding checkbox in excelmicrosoft excel vba programmingvba copy cellshow to delete blank columns in exceldml in sqllookup on excelvba excel selectvlookup step by stepremoving duplicates excelfind password excel protected sheetvba paste specialdeveloping dashboardscombobox excel 2013check duplicates in excelifs statement exceldo while statement vbahow to unhide all sheetsvba list files in foldertick mark in excel 2007spreadsheet sort functionexcel dashboard examples freeformat excel vbavba write to text file