How to Use "Find" in Excel VBA Macros

Step-by-Step Guide

1. 1

   Step 1: Learn the following key concepts of the FIND command: The syntax of .Find is: expression.Find(What

   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.
2. 2

   Step 2: LookIn

   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. , Save the workbook as "Overall Status" and Sheet1 as "Article Views and Other". , 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.LifeGuide Hub.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. , 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. , 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. ,,, Macro3_FindRelated End Sub Sub Macro3_FindRelated() ' Finds a Related LifeGuide Hub 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 ="*]" 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, *] 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.
3. 3

   Step 3: LookAt

4. 4

   Step 4: SearchOrder

5. 5

   Step 5: SearchDirection

6. 6

   Step 6: MatchCase

7. 7

   Step 7: MatchByte

8. 8

   Step 8: SearchFormat) Expression (Required): is any valid range Object.

9. 9

   Step 9: 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).

10. 10

    Step 10: Open a new Excel workbook.

11. 11

    Step 11: In your browser

12. 12

    Step 12: go to https://www.LifeGuide Hub.com/User:Chris-Garthwaite and do (View More) under Articles Started

13. 13

    Step 13: and select just to the left of the first top one down to just to the right of the bottom one's Views

14. 14

    Step 14: and copy it.

15. 15

    Step 15: Note that if you were to use Excel's menu-driven Find command

16. 16

    Step 16: it would return in the upper left corner (to the left of the formula bar) the cell reference it found

17. 17

    Step 17: if any

18. 18

    Step 18: for the string you searched on in the given range of cells

19. 19

    Step 19: 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.

20. 20

    Step 20: Note that the underline used at the right end of code lines is to extend the line to the next line down

21. 21

    Step 21: so the next line is in fact part of the entire sub-procedure or expression.

22. 22

    Step 22: You can easily adapt this code by changing the sheet name reference and the cell range reference

23. 23

    Step 23: and if it's not an Article you're searching for

24. 24

    Step 24: you can certainly change that everywhere as well.

25. 25

    Step 25: Here are two more macros that work together to update Related LifeGuide Hubs: 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"

26. 26

    Step 26: 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.

Detailed Guide

About the Author