How to Use "Find" in Excel VBA Macros

Learn the following key concepts of the FIND command:

• The syntax of .Find is:
  • expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
  • Expression (Required): is any valid range Object. So if we take an example then the range would be Range(“A1:A” & lastRow) where lastRow has been DIMensioned as type long and there is a statement: lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row (or something similar) preceding its use in the .Find required expression.
  • What (Optional Variant): is the “Search value”
  • After (Optional Variant): The cell after which you want the search to begin.
  • LookIn (Optional Variant): The type of information. (xlValues or xlFormulas)
  • LookAt (Optional Variant): Can be one of the following XlLookAt constants: xlWhole or xlPart.
  • SearchOrder (Optional Variant): Can be one of the following #** XlSearchOrder constants: xlByRows or xlByColumns.
  • SearchDirection: Can be one of these XlSearchDirection constants. xlNext default xlPrevious
  • MatchCase (Optional Variant): True to make the search case sensitive. The default value is False.
  • MatchByte (Optional Variant): Used only if you've selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.
  • SearchFormat (Optional Variant): The search format.

You can adapt the following code to your own uses by copying it (down to where it says #** End Sub) in Advanced Editing and pasting it to a word processor and doing a REPLACE ALL for "#** " (w/o the quotes but with the trailing space please). Then in Excel Preferences, set the Ribbon Developer status to Checked or On so you can work with macros. On the Ribbon, click on Developer, and then the Record icon and just click in cell A1 and then do Stop Recording (of your dummy macro). Click on the Editor icon button and copy your REPLACE(d) ALL text from your word processor from here right over the macro, and save the workbook.

Open a new Excel workbook. Save the workbook as "Overall Status" and Sheet1 as "Article Views and Other".

In your browser, go to https://www.wikihow.com/User:Chris-Garthwaite and do (View More) under Articles Started, and select just to the left of the first top one down to just to the right of the bottom one's Views, and copy it. Make sure you don't get any excess info, just the articles and number of views. Then paste that data into the "Article Views and Other" worksheet at cell C17. Then, in your browser, go to https://www.wikihow.com/User:Xhohx and copy all 100 expanded (View More) Articles and Views from there too please, and paste those in the cell right below the ones you did before.

Then. on your toolbar, choose Macros, Macros, and Run the following pasted-in REPLACE(d) ALL macro to test it.

• • Sub Macro2_FindArticle()
  • ' Finds an Article String, returns cell address and goes to Article cell
  • ' Macro2_FindArticle Macro
  • Dim oSht As Worksheet
  • Dim StrFinder As String
  • Dim lastRow As Range
  • Dim aCell As Range
  • Set oSht = Sheets("Article Views and Other")
  • Set lastRow = ActiveWorkbook.Sheets("Article Views and Other").Range("C17:C217")
  • Application.ScreenUpdating = True
  • Do Until StrFinder <> ""
  • StrFinder = Application.InputBox _
  • (Prompt:="Article Name or string to search for: ", _
  • Title:="Article Search", _
  • Type:=2)
  • Set aCell = lastRow.Find(What:=StrFinder, LookIn:=xlValues, _
  • LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  • MatchCase:=False)
  • If Not aCell Is Nothing Then
  • MsgBox "Value Found in Cell " & aCell.Address
  • End If
  • aCell.Select
  • Application.ScreenUpdating = False
  • Exit Sub
  • Loop
  • End Sub
  • You should have a list of articles something like this redacted image, probably without the colored background.

Note that if you were to use Excel's menu-driven Find command, it would return in the upper left corner (to the left of the formula bar) the cell reference it found, if any, for the string you searched on in the given range of cells, but you would have to hit the right arrow and left arrow (or some such combination) to select the actual cell as the sole ActiveCell I believe. At least, that's how Excel functions in this author's experience -- not obviously high lighting the cell for me or obviously selecting it, as it used to do in older versions of Excel.

Note that the underline used at the right end of code lines is to extend the line to the next line down, so the next line is in fact part of the entire sub-procedure or expression.

You can easily adapt this code by changing the sheet name reference and the cell range reference, and if it's not an Article you're searching for, you can certainly change that everywhere as well.