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


vlookup excel easy stepsdelete duplicate data excelsql questions and answers pdfhow do i lock excel spreadsheetmulti select listboxsumif on exceleliminate empty rows in excelsas programming for dummiespivot table commandexcel task scheduler templateexcel vba and macrosproject management plan excel templatesql and sas trainingmicrosoft access remove duplicatesproject dashboard pptinsert rows in excel shortcutvb6 interview questions and answersexit vbaexcel vba add new worksheetautofilter macrosas sql tutorialexcel datevalueexcel macro saveasexcel vba save as xlsxhow to create activex controlpdf excel vba programmingexcel counting duplicatesremove hyperlink in excelexcel macro rowsexcel sql connectionvba if statement multiple conditionsapplication.wait excel vbaexcel 2013 pivot table tutorialhow to protect spreadsheet with passwordexcel template for tracking tasksvba classeshow to sumif in excelvlook functionconditional vlookup in exceltesting plan template excelpivot table setupsum function in vbashortcut to switch between excel workbooksproject status report dashboard templatemacro basics excelhow do you remove a hyperlink in excelcombo box excel 2007learn to code vbaconsolidate worksheetsvba sql selectvbscript read text filehow to remove duplicates in excel sheethard return in excelisna function vlookupexcel vba click buttoniserror formulavba change file nameunprotect excel 2007create button vbaexcel sort alphabeticallyvba workbook activaterename files using excelhow to use vlookup excel 2010how to create userform in excel vbavb automation errorhow to protect formulas in excel 2007how to insert a new row in excelexamples of gantt charts in excelcolor by code worksheetssql query from excelcreating dynamic charts in excelvba excel pastespecialexcel 2010 combo boxclear cell vbaexcel 2010 iferrorexcel vba unhide all sheets