# VBForums CodeBank > CodeBank - Other >  Excel Find Method in Excel VBA (Any version of Excel)

## Siddharth Rout

*I* have been contemplating on writing this tutorial for a long time as I have found a lot of questions revolving around this topic. However due to the tight schedule that I am in, I was not getting the time for it. Surprisingly today I have no work and I am absolutely free. So the first thing that I did was open my pending work list (_Old habits die hard_) and saw that I had to write a tutorial on .Find  :Big Grin:  so here it is…

Most of us use loops in case we wanted to find something in Excel.

For Example, Let’s say

Our data is in sheet1 (Workbook Attached) from Cell A1 to A65000 and the data is like this




> A1 ~~> 1
> A2 ~~> 2
> A3 ~~> 3
> A4 ~~> 4
> A5 ~~> 5
> ‘
> ‘
> ‘
> A65000 ~~> 65000


Now suppose we want to find which cell has say 10000. The primitive way was to loop through each cell and find which cell had that value. For Example



```
Sub Sample()
    Dim oSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String
    Dim t As Long
    
    t = GetTickCount
    
    On Error GoTo Err
    
    Set oSht = Sheets("Sheet1")
    
    lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
    
    strSearch = "10000"
    
    For i = 1 To lastRow
        If oSht.Range("A" & i).Value = strSearch Then
            MsgBox "Value Found in Cell " & oSht.Range("A" & i).Address & vbCrLf & _
            "and it took " & GetTickCount - t & " milliseconds"
            Exit Sub
        End If
    Next i
  
    Exit Sub
Err:
    MsgBox Err.Description
End Sub
```

This method of looping is not ‘_wrong_’ but yes it is very slow as compared to Excel’s inbuilt “.Find” Tool. The above sub executed in 109 milliseconds on my laptop.

In this tutorial, I will stress on how to use .Find to make your search faster.

The syntax of .Find is

_expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)_

Where 

_Expression (Required)_: is any valid range Object. So if we take the above example then the range would be Range(“A1:A” & lastRow)
_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.

Now let’s try and incorporate .Find to find the data that we want.



```
Sub Sample1()
    Dim oSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String
    Dim t As Long
    Dim aCell As Range
    
    t = GetTickCount
    
    On Error GoTo Err
    
    Set oSht = Sheets("Sheet1")
    
    lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
    
    strSearch = "10000"
    
    Set aCell = oSht.Range("A1:A" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        MsgBox "Value Found in Cell " & aCell.Address & vbCrLf & _
        "and it took " & GetTickCount - t & "milliseconds"
    End If
    
    Exit Sub
Err:
    MsgBox Err.Description
End Sub
```

*The above sub took 6 milliseconds as compared to 109 milliseconds on the ‘looping’ sub !!!*

Let’s now take different scenarios on how to work with ".*Find*"

The other scenarios that we will work on are as follows…

1)	Find Values in Cell Value (*Covered above. Example in Sheet1*)
2)	Find Values in Cell Formula (Example in Sheet2)
3)	.FindNext (Example in Sheet3)
4)	Making .Find work as Vlookup() formula (Example in Sheet4)

If there are any other scenarios that you are finding difficulty with then simply create a new thread in Office Development and I will try to help you if I can  :Smilie:

----------


## Siddharth Rout

*Scenario 2*

*Find Values in Cell Formula (Example in Sheet2)*

Lets say our data is as follows




> B1 ~~> 1
> B2 ~~> 2
> B3 ~~> 3
> B4 ~~> 4
> B5 ~~> 5
> 
> 
> 
> B100 ~~> 100
> ...


Now I want to Find the word MAX in the formula and replace it with say SUM so using .Find we can achieve it in the following manner. 

Note that since we are searching for values in the formula then *LookIn* takes the value of *xlFormulas*



```
Sub Sample2()
    Dim oSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String
    Dim aCell As Range
    
    On Error GoTo Err
    
    Set oSht = Sheets("Sheet2")
    
    lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
    
    strSearch = "MAX"
    
    Set aCell = oSht.Range("A1:A" & lastRow).Find(What:=strSearch, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        MsgBox "A"
        aCell.Formula = Replace(aCell.Formula, strSearch, "SUM")
    End If
    
    Exit Sub
Err:
    MsgBox Err.Description
End Sub
```

----------


## Siddharth Rout

*Scenario 3*

*.FindNext (Example in Sheet3)*

Let’s say our data is as follows



> A1 ~~> 1
> A2 ~~> 2
> A3 ~~> 3
> A4 ~~> 4
> A5 ~~> 5
> ‘
> ‘
> A27~~> 2
> ‘
> ...


If you have noticed that Cell A2, A27, A45 has the same value which is 2

So if I want to find all "2" 's one after the other then how do I do that using *.Find*. It is very simple. We use *.FindNext* in a loop.

See example below.



```
Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim ExitLoop As Boolean
    Dim SearchString As String, FoundAt As String
    
    On Error GoTo Err
    
    Set ws = Worksheets("Sheet3")
    Set oRange = ws.Columns(1)

    SearchString = "2"
    
    Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        Set bCell = aCell
        FoundAt = aCell.Address
        Do While ExitLoop = False
            Set aCell = oRange.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                FoundAt = FoundAt & ", " & aCell.Address
            Else
                ExitLoop = True
            End If
        Loop
    Else
        MsgBox SearchString & " not Found"
    End If
    
    MsgBox "The Search String has been found these locations: " & FoundAt
    Exit Sub
Err:
    MsgBox Err.Description
End Sub
```

----------


## Siddharth Rout

*Scenario 4*

*Making .Find work as Vlookup() formula (Example in Sheet4)*

Lets Say we have a database (see picture attached) and a range where data needs to be updated (see picture attached)

Now suppose we have to find the capitals for the relevant companies then we can use .Find() to get the relevant companies

For Example



```
Sub Sample()
    Dim ws As Worksheet
    Dim DataRange As Range, UpdateRange As Range, aCell As Range, bCell As Range
    
    On Error GoTo Err
    
    Set ws = Worksheets("Sheet4")
    Set UpdateRange = ws.Range("B5:B16")
    Set DataRange = ws.Range("J5:J16")
    
    For Each aCell In UpdateRange
        Set bCell = DataRange.Find(What:=aCell, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
    
        If Not aCell Is Nothing Then
            aCell.Offset(, 1) = bCell.Offset(, 1)
        End If
    Next
    
    Exit Sub
Err:
    MsgBox Err.Description
End Sub
```

----------


## Pradeep1210

Nice tutorial  :Thumb:

----------


## manandpc

Here is a subroutine that can be used to find the last cell in a sheet that contains a string.

First, make it similar to the Ctrl-F (excel default find), but more simple
Second, adjust the where about to be in the center (sort of) on the screen of the cell found!

Note: if not found, do nothing, that can be improve!



```
Sub FindLast()
    Static sWhat
    sWhat = InputBox("Find What:", "FIND LAST", sWhat)
    If sWhat <> "" Then
        Dim rFound As Range
        
        On Error Resume Next
        Set rFound = Cells.Find(What:=sWhat, _
            After:=Cells(Cells.Rows.Count, Cells.Columns.Count), _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
        On Error GoTo 0
        
        If Not rFound Is Nothing Then
            ' MsgBox rFound.Row & " " & rFound.Column
            iRowOffset = 10
            rFound.Offset(iRowOffset, 0).Select
            If rFound.Row <= iRowOffset Then iRowOffset = rFound.Row - 1
            rFound.Offset(-iRowOffset, 0).Select
            iColOffset = 4
            rFound.Offset(0, iColOffset).Select
            If rFound.Column <= iColOffset Then iColOffset = rFound.Column - 1
            rFound.Offset(0, -iColOffset).Select
            rFound.Select
            
            ' sYesNo = InputBox("Copy the last month 3 columns ? (Y)", "COPY LAST MONTH")
            ' If sYesNo = "y" Or sYesNo = "Y" Then
            '    Range(rFound.Offset(0, -2), rFound.Offset(0, 0)).Select
            '    Application.Selection.Copy
            '    rFound.Offset(0, 1).Select
            '    Application.ActiveSheet.Paste
            '    rFound.Offset(0, 1).Select
            '    Application.CutCopyMode = False
            ' End If
        End If
    End If
End Sub
```

 :Big Grin:

----------


## Colonguero

Hi koolsid,

Good tool.
Could you help me with  (1) adding deletion of the rows that where found by .find and (2) find and list of about 50 values (and later delete the rows)?

Foer (1) I used scenario 3 and replaced the "MsgBox "The Search String has been found these locations: " & FoundAt" by "Range(FoundAt).EntireRow.Delete".
This worked find for some hits but  "Range" reported an error for a long string for "FoundAt" (e.g. 70).

I would apprecitate any help on this.

----------


## vbanewb

Hi Siddharth,
Right now I am using below macro to replace values in my large data. But the problem is, The replace or replaced values are changing with every data set and if required value not found the macro give error. Can anyone help me, how I use IF condition with Cells.replace. For example IF D found then run this block of code otherwise jump to next value.




> Sub ReplaceAll()
> 
> Application.ScreenUpdating = False
> 
> Sheets("Data").Select
> Range("A1").Select
> 
> Cells.replace what:="D", Replacement:="", LookAt:=xlPart, SearchOrder:= _
>     xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
> ...


Any help will be appreciated

Thanks

----------

