How to Search a Cell Range for a Match in Excel VBA

1. Open the Visual Basic Editor (VBE). Click the 'Developer' tab in Excel and then click 'Visual Basic.'
2. Click 'Insert' and then click 'Module.'
3. Cut and paste the following code into the blank window:Sub Find_First()Dim FindString As StringDim Rng As RangeFindString = InputBox('Enter a Search value')If Trim(FindString)
'' ThenWith Sheets('Sheet1').Range('A1:A20')Set Rng = .Find(What:=FindString, _After:=.Cells(.Cells.Count), _LookIn:=xlValues, _LookAt:=xlWhole, _SearchOrder:=xlByRows, _SearchDirection:=xlNext, _MatchCase:=False)If Not Rng Is Nothing ThenApplication.Goto Rng, TrueElseMsgBox 'Nothing found'End IfEnd WithEnd IfEnd Sub
4. Change the range to suit your needs. The range in the code is specified as A1 through A20 (Range('A1:A20')). For example, if you want the range to be B1 through B100, change the range to 'Range('B1:B100').'
5. Press 'F5' to run the macro. A pop-up window will appear on the worksheet. Type the number or phrase you want to search for into the box and then press 'Enter.'