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


pivot table limitsexcel chart basicscheckbox excel vbacountif functionsrgb excelassign macro to command buttonvba sampleintroduction to vba programmingdml dllvba open text filehow to unhide exceltrim function excelexecutive project dashboard templatedelete a row vbahow to create a project timeline in excelvba excel formulasexcel for loopsexcel function substituteremove password protection excel 2010removing characters in excelexcel hyperlink to pdfchart wizard excel 2013excel macro to select a cellhow to merge columns in excelworkbooks.open vbahow to merge cells in excel with multiple dataexcel based dashboardsexcel vba global variableisna excelpivot table tutorial excel 2007excel formulas and functions for dummiesshortcut insert row excelclose excel in vbahow to unhide all cells in exceldelete duplicate cells in excelsearch duplicate in excelproject timeline powerpoint template freeeliminating duplicate rows in excelworkbook and worksheetproject portfolio dashboard excelvba form excelexcel graphs exampleshlookup function in excelvbscript read excel filelistbox additem vbafor loop in vba excelexcel deleting duplicatesvba pdf to excelexcel formula replacevba application pathkpi dashboard excel templatesfree excel dashboards templatesms excel advanced tutorialvlookup and isnavba delete worksheetlookup functions in excelvba access recordsetmulti select listboxinsert column vbadelete blank rows excel 2010sales dashboard excel templatemacros in excel tutorial for beginners pdfhow to count repeats in excelloops excelsumif excel tutorialbasic sql questions and answersunprotect excel 2007 workbookproject planning spreadsheetmake pivot table in excelexcel how to write a macroexcel macro referencegreater than formula excelexcel vba display userforminsert row in excel shortcutvisual basic command buttonvlookup examples 2010excel calculate weeks between two dateshow to remove password from protected excel sheet