How to Use “Find” in Excel VBA Macros

by 
You will learn to use "Find" for your macros written in Excel Visual Basic for Applications (VBA).

Steps

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 xlPreviousMatchCase (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.
  • 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 . 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 MacroDim oSht As WorksheetDim StrFinder As StringDim lastRow As RangeDim aCell As RangeSet oSht = Sheets(“Article Views and Other”)Set lastRow = ActiveWorkbook.Sheets(“Article Views and Other”).Range(“C17:C217”)Application.ScreenUpdating = TrueDo 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 ThenMsgBox “Value Found in Cell ” & aCell.AddressEnd IfaCell.SelectApplication.ScreenUpdating = FalseExit SubLoopEnd SubYou should have a list of articles something like this redacted image, probably without the colored background.
  • 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.

Here are two more macros that work together to update Related minHour:

  • Sub Macro10()’ Macro10 Macro’ Keyboard Shortcut: Option+Cmd+nWindows(“OVERALL STATUS.xlsm”).ActivateSheets(“Relateds”).SelectApplication.Goto Reference:=”TopRow”Application.CutCopyMode = FalseSelection.Delete Shift:=xlUpActiveWorkbook.Names.Add Name:=”TopRow”, RefersToR1C1:=”=Relateds!R166″Range(“B166”).SelectSelection.CopyApplication.Goto Reference:=”Searcher”‘ Searcher is a Defined Name Variable comprised of the Article Names’ and Views pasted AS TRANSPOSED from rows TO COLUMNS in row 1.Macro3_FindRelatedEnd SubSub Macro3_FindRelated()’ Finds a Related wikiHow String and goes to Article cell,’ where User then Arrow keys down and inputs a 1,’ indicating that that Columnar Article is a Related for the Row Article.’ There are both row totals and columnar totals to keep track of Total Relateds/Article’ and how many Articles the Columnar Article is a Related for.’ Uses the Named Variable Range, “Searcher”, on the worksheet “Relateds” as a Range Object’ Macro3_FindRelated MacroDim oSht As WorksheetDim StrFinder As StringDim aCell As RangeDim rng As RangeSet oSht = Sheets(“Relateds”)Windows(“OVERALL STATUS.xlsm”).ActivateSheets(“Relateds”).SelectSet rng = Worksheets(“Relateds”).Range(“Searcher”)Application.ScreenUpdating = TrueDo Until StrFinder “”StrFinder = Application.InputBox _(Prompt:=”Article Name or string to search for: “, _Title:=”Article Search”, _Type:=2)Set aCell = rng.Find(What:=StrFinder, LookIn:=xlValues, _LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _MatchCase:=False)aCell.SelectApplication.ScreenUpdating = FalseExit SubLoopEnd SubIn cell H1 is xxxxxxxxxxxxxxxxxx and I1 is blank. From J1 to ET1 are the Article Titles and in EU1 is Other — a reconciling account rarely used. From J4 to EW4 are the Article views and their Total. From J6 to ET6 are the Article#s from 141 down to 1 (most recent at far left, oldest at far right). All of that was pasted as Transposed from cell range B10:G153.In cell A166 is [xxxxxxxxxxxxxxxxxxx| and that is copied far down below, as it gets deleted with continued usage.In cell B166 is the formula =IF(ISERR(MID(A166,D166+2,(E166-D166)-2)),””,MID(A166,D166+2,(E166-D166)-2)) which returns the value, xxxxxxxxxxxxxxxxxx.In cell D166 is the formula =IF(ISERR(FIND(“[“,A166)),””,FIND(“[“,A166)), which returns the value of 1.In cell E166 is the formula =IF(ISERR(FIND(“|”,A166)),””,FIND(“|”,A166)), which returns 21.In cell A9 is the formula =”*[[“&B9&”|How to “&B9&”]]”When the formula in A9 is copied to A10, and the Article Name Find the Diagonal of a Square Using Its Area is in B10, A10 returns the value, *[[Find the Diagonal of a Square Using Its Area|How to Find the Diagonal of a Square Using Its Area]]Whether copied from the first sheet in the workbook, Article Views and Other, or from this sheet, Relateds, these ready-to-become-Related formatted entries in column A are then pasted, if they’re appropriate How-To’s for the article being updated, to cell B167 and below before the following macro is run and command v pasting is done into the InputBox to locate the Article in the top Searcher area to arrow key down and update the proper row (Article) with a 1 to.
  • Sub Macro10()
  • ‘ Macro10 Macro
  • ‘ Keyboard Shortcut: Option+Cmd+n
  • Windows(“OVERALL STATUS.xlsm”).Activate
  • Sheets(“Relateds”).Select
  • Application.Goto Reference:=”TopRow”
  • Application.CutCopyMode = False
  • Selection.Delete Shift:=xlUp
  • ActiveWorkbook.Names.Add Name:=”TopRow”, RefersToR1C1:=”=Relateds!R166″
  • Range(“B166”).Select
  • Selection.Copy
  • Application.Goto Reference:=”Searcher”
  • ‘ Searcher is a Defined Name Variable comprised of the Article Names
  • ‘ and Views pasted AS TRANSPOSED from rows TO COLUMNS in row 1.
  • Macro3_FindRelated
  • End Sub
  • Sub Macro3_FindRelated()
  • ‘ Finds a Related wikiHow String and goes to Article cell,
  • ‘ where User then Arrow keys down and inputs a 1,
  • ‘ indicating that that Columnar Article is a Related for the Row Article.
  • ‘ There are both row totals and columnar totals to keep track of Total Relateds/Article
  • ‘ and how many Articles the Columnar Article is a Related for.
  • ‘ Uses the Named Variable Range, “Searcher”, on the worksheet “Relateds” as a Range Object
  • ‘ Macro3_FindRelated Macro
  • Dim oSht As Worksheet
  • Dim StrFinder As String
  • Dim aCell As Range
  • Dim rng As Range
  • Set oSht = Sheets(“Relateds”)
  • Windows(“OVERALL STATUS.xlsm”).Activate
  • Sheets(“Relateds”).Select
  • Set rng = Worksheets(“Relateds”).Range(“Searcher”)
  • Application.ScreenUpdating = True
  • Do Until StrFinder “”
  • StrFinder = Application.InputBox _
  • (Prompt:=”Article Name or string to search for: “, _
  • Title:=”Article Search”, _
  • Type:=2)
  • Set aCell = rng.Find(What:=StrFinder, LookIn:=xlValues, _
  • LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  • MatchCase:=False)
  • aCell.Select
  • Application.ScreenUpdating = False
  • Exit Sub
  • Loop
  • End Sub
  • In cell H1 is xxxxxxxxxxxxxxxxxx and I1 is blank. From J1 to ET1 are the Article Titles and in EU1 is Other — a reconciling account rarely used. From J4 to EW4 are the Article views and their Total. From J6 to ET6 are the Article#s from 141 down to 1 (most recent at far left, oldest at far right). All of that was pasted as Transposed from cell range B10:G153.
  • In cell A166 is [xxxxxxxxxxxxxxxxxxx| and that is copied far down below, as it gets deleted with continued usage.
  • In cell B166 is the formula =IF(ISERR(MID(A166,D166+2,(E166-D166)-2)),””,MID(A166,D166+2,(E166-D166)-2)) which returns the value, xxxxxxxxxxxxxxxxxx.
  • In cell D166 is the formula =IF(ISERR(FIND(“[“,A166)),””,FIND(“[“,A166)), which returns the value of 1.
  • In cell E166 is the formula =IF(ISERR(FIND(“|”,A166)),””,FIND(“|”,A166)), which returns 21.
  • In cell A9 is the formula =”*[[“&B9&”|How to “&B9&”]]”
  • When the formula in A9 is copied to A10, and the Article Name Find the Diagonal of a Square Using Its Area is in B10, A10 returns the value, *[[Find the Diagonal of a Square Using Its Area|How to Find the Diagonal of a Square Using Its Area]]
  • Whether copied from the first sheet in the workbook, Article Views and Other, or from this sheet, Relateds, these ready-to-become-Related formatted entries in column A are then pasted, if they’re appropriate How-To’s for the article being updated, to cell B167 and below before the following macro is run and command v pasting is done into the InputBox to locate the Article in the top Searcher area to arrow key down and update the proper row (Article) with a 1 to.

Tips

  • The Type:=2 code in the Application.InputBox section means that it is a string. Type 0 is a formula; type 1 is a number; type 4 is a logical value (True or False); type 8 is a cell reference, as a range object; type 16 is an error value, such as #N/A; and type 64 is an array of values.

Leave a Comment