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


use of pivot table in excel 2010how to create pivot chart in excel 2007v look up exampleexcel vba vlookup exampleif statements excelldescending order dates exampleturn on macros in excel 2007sample gantt chart excel templatehow to create pivot tables in excel 2007how to insert macros in excelsql vba excelstacked column graph excel 2010vba while statementhow to merge cells in excel 2010task planner templatehow to use the trim function in excelexcel vba templatesexcel insert a columnexcel to sql queryexcel task management template freeexcel vba easyforgot password of excel protected sheetchart tools layout tab excel 2013reverse cell order excelexcel macro writinghow to write an if statement in excelchart creation in excelworksheetfunctionfilter vbachart types in excel 2010how to unlock protected cells in excelremove duplicates excel 2010match vbavba adodb.commandmath and trig functions in excelexcel vba delete selected rowsfinancial analyst interview questions and answers pdfxml vbacountif formula exceltrim function vbapivot excel tableadvanced pie charts in excelhse meeting minutes templateexcel vba programming for dummies torrenthandy excel macrossave xls as csvgetting rid of duplicates in excelvba workbookspivot table auto refreshmicrosoft office gantt chart templatehow to unprotect password protected excel sheetexcel consolidate sheetsuseful excel macrorecord macro in excelformula lookupvba open files in folderconsolidate multiple worksheetsmicrosoft office project management templatesvba excel copy worksheet to another workbookhow to put title on excel graphexcel 2007 vba examplesexcel vba rowuseful excel macrosvlookup functions in excelmacro programing in excelmsgbox input vbaexcel look for duplicatesvlookup with exampleexcel isnumbersyntax excel definitionunprotect excel worksheet without passwordwriting an excel macrosort sql statementset autofilter vba