Free Microsoft Excel 2013 Quick Reference

[Solved] VBA : Cells.Find function

When I try using Cells.Find(...) in Visual Basic, I've been getting an error message saying With or Block Variable Not Set.

I have Office XP on my computer at home and 2002 on my work computer. The call works on 2002, but doesn't seem to work in XP. Does anybody know why this would be or how to fix it?

I've even tried recording a macro, and Find works from the Edit menu. Then, I go into VBA and try to run the macro and it gives me the error, even though excel just went through the process and it worked fine. I don't get it.

Thanks for any help!

Post your answer or comment

comments powered by Disqus
Im writing a macro that will read data from one workbook, find the data in another workbook, copy the column associated with that data, and paste it into a new sheet. Im having trouble with the cells.find function... when i record a macro the code comes out like this:

Cells.Find(what:="Node Info: 443", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Activate

if i remove the line of code, it no longer works... but I dont know which sheet in the workbook the data will be on all the time so cannot include the sheet in my own code.

now my code:

Sub forloop()
c = 6

For i = 1 To 35
Windows("Monthly DP TC - Syracuse").Activate
Cells(i, 40).Select
If Not IsEmpty(Cells(i, 40).Value2) Then
mycell = ActiveCell.Value
mySearch = "Node Info: " & mycell
If Not UCase(Right(mycell, 5)) = "TOTAL" Then

Windows("VoIPReady - weekly - SYRNY - TW-CentralNY 2007-07-04.xls").Activate
Application.FindFormat.NumberFormat = "General"

Set Z = Cells.Find(what:=mySearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True)

If Not Z Is Nothing Then

myrow = ActiveWindow.RangeSelection.Row
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveSheet.Range(Cells(c, 1), Cells(c, 24)).Select
c = c + 1

MsgBox "Can't find " & mySearch
End If
End If
End If
Next i

End Sub

the problem is that the macro refuses to find the node number data using the cells.find command even though it exists on the searched workbook. It works when you provide it with the sheet that the data exists on, such as
but I do not know what sheets each node data exists on... thats why im doing a find.


Hi guys, I wrote a custom find function to search for a list of numbers across a bunch of worksheets (6 in total). A cell reference is sent to the function and it basically returns the worksheet name and address for the first occurence, or nothing if not found.

Now I realise that the number of cells that need to be checked is fairly severe across 6 worksheets (finding 65 numbers * 65536 * 256), but this still takes about 1 minute to execute... is that about right? Code was hacked together quickly and is nothing special... Is there a quicker way?

    Dim ws As Worksheet 
    Dim my_cell As Variant 
    Dim result As Variant 
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
    On Error Resume Next 
    For Each my_cell In my_range 
        For Each ws In Application.Worksheets 
            If ws.Name  "Numbers" Then 
                result = ws.Name & "@" & ws.Cells.Find(my_range.Value, [a1], xlValues).Address 
                If IsNumeric(Right(result, 1)) Then 
                    My_Find = result 
                    Exit Function 
                End If 
            End If 
        Next ws 
    Next my_cell 
    On Error Goto 0 
    My_Find = "" 
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi All,
Please help if possible, im not a programmer but as you will see below but i need help.
I have marked the problem with ****. what the code is supposed to do is go to sheet 2 A1 and copy the value, go to sheet 1 and search for the value, if it is found then go to sheet 2 and go over 2 column and select the value, go to sheet 1 and go over 2 columns and paste the value. tis this works until it cannot find the value and returns Nothing, which causes the program to crash. i need some sort of validation that will tell it just to move ionto the next one and skip it. need help ASAP. thanksin advance

code: (vba)

Let n = "A"
Let p = "1"
Let q = "C"

Do While p

The cells.find does it work if I want it to look in two sheets?

Cells.Find(what:=X, After:=ActiveCell, LookIn:=xlFormulas, _ 
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What do I have to change?


I have a piece of code and I would like to set the result of a Cell.Find as a Dim to be used in further selections.

Dim a As String

a = Cells.Find(What:="fi doc. line item").Value

Cells.Find(What:="fi doc. line item", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Range("C, a").Activate
    Range(Selection, Cells(1)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
I want it to select the row of the result in both rows and Range as a.

Not doing a very good job of explaining here, partly becasue its a recorded macro.

Hopefully somebody can understand what I'm trying to do and help.

Hi there,

I'm trying to create a macro that runs a search for the cell I have selected.

It would run the search in another workbook and return a value.

I have everything sorted (I think) but I cannot get excel to search for the selected cell, it will only search for a value eg A4 or A5 etc, which I have to change every time I want to run the macro.

    Cells.Find(What:="??????", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
In the ??? I have tried putting all sorts but without much luck, I have tried active.cell and other such guesses.

Any help you could offer would be greatly appreciated.


I can use the HLOOKUP formula to return an approximate match by simply using the FALSE parameter at the end of the formula.
Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 =HLOOKUP("*"&A1&"*",A1:Z2,2,FALSE) 

How can I replicate the approximate parameter in the Find function (or similar VBA function)?

I'm using the following code which only returns exact matches:

    Dim InstType As String 
    On Error Resume Next 
    InstType = [ 'report'!].Range("1:1").Find(What:=Range(E14), After:=Cells(1, 1), _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _ 
    SearchDirection:=xlNext, MatchCase:=True)(2, 1).Address 
    If strAddress  vbNullString Then 
        Range("A7") = Range(InstType) 
        MsgBox "There is no data" 
    End If 
    On Error Goto 0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I can change it to not look at case, but beyond that I'm confused. Thanks for your help.


I need a bit of help with the .FIND function.
I am still a VBA newbie so I get stumped quite a bit.
I would like some help on why the last line of the code doesnt work.
I get the error message "Run Time Error '91', Obhject variable or With block variable not set"

I used this line on another macro similar to this one :
'AA = Sheets("Activities").Columns("A:A").Find(issuetype, , xlValues, xlWhole, SearchOrder:=xlByColumns).Row

and it works, so I dont understand why it doesnt work this time!
Any help would be greatly appreciated.
Thank you.

 srcwbk = Workbooks("munzees") 
Set TgtWbk = ThisWorkbook 
Set srcws = srcwbk.Sheets("Munzees") 
Set tgtws = TgtWbk.Sheets("munzee") 
a = 2 
b = 2 
Do While srcws.Cells(a, 1).Value  "" 
    latitude = srcws.Cells(a, 1).Value 
    longitude = srcws.Cells(a, 2).Value 
    aa = tgtws.Columns(1).Find(latitude, , xlValues, xlWhole, SearchOrder:=xlByColumns).Row 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi all

if anybody could help me with the following bit of code I would really appreciate it...I have only just stated trying to use VBA so hopefully for somebody out there it's simple.

Am trying to search a range on a spreadsheet using the following code;

With Range("a1:a99") 
    strCheck = .Find(what:=strPosition, After:=.Cells(1, 1), LookIn:=xlValues, _ 
    Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1) 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and I then use strCheck in another bit of the code, which I need to run regardless of whether there is a match or not. It works fine when there is a match (i.e. a cell in the range matches strPosition) however when there is no match I get the message;

runtime error '91' object variable or with block not set

Is there a way I can get around this? I have tried a few different approaches but none of them very successful. Is there a way to set the "find" function to return "" if it can't find a match? Or is "find" not really the best way to approach this?




To make the story short, the find function sometimes doesn't find a the value in a range though it's there. However if executed on a different PC, it might find the value. I'm assuming it has to do with a state fo the Find feature that Excel keeps from a previous find (similiar to pasting text into Excel that based on previous text to column settings might or might not be correctly split into multiple cells.)

A typical VBA code I'm using looks like this:

 ws = wb.Worksheets("CO") 
Set ws2 = ActiveWorkbook.Sheets(1) 
nlastrow = FindLastRow(ws) 
With ws2 
    For i = nlastrow To 2 Step -1 
        s = .cells(i,2) 
        Set c = ws.Columns(1).Find(s, Lookat:=xlWhole) 
        If c Is Nothing Then 
        End If 
    Next i 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Long Story - the VBA codes (multiple files) are executed every single day since about 2 yrs without a problem. About 3 months ago, suddenly one of the Find functions didn't find the cell anymore. The same happened on a second PC at exactly the same position in the code at exactly the same value - but not on a third PC. Back then due to time constraints, but also the small size of the repetitions I decided to implement a work-around by using a loop instead of getting to the bottom of this. The problem moved than from one Find code to another one in the same file. I used the same work-around and we didn't experience any problems until 2 weeks ago. Since 2 weeks it happened only on two days and only on one PC that the Find function again doesn't seem to find the value. When I ran the same code on a different PC on the same data, no issues. Thsi time, however I would delay the whole process quite a lot if I would implement the work-around and I probably only move the issue further to the next Find call. Hence I'm trying to find out what the cause could be.

Did anybody come across such an intermittend behaviour? Might this be related to previously executed Find functions/methods?

Thanks in advance,


I'm trying to retrieve data from worksheets in reports in excel which I will have to do from time to time. The best way I can think to do this is using control + find functions to find the specific data element I am looking for and then to select a nearby cell based on this cell. I am thinking - if my find search takes me to cell A21, then I want to select cell C21. I can't figure out how to do this though. My VBA code looks like below:

Cells.Find(What:="name and address", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

I know I need to do something with the 'Range("B14").Select' line but don't know what. I've tried 'ActiveCell.Offset(0, 1).Select' but can't get this to work. Any help/advice would be greatly appreciated...


Hi, I am relatively new to vba, but my question is:
Can you use the find function and array to loop through different 'what:=' search terms?

For example:
Sub ref()

Dim RefNum(0 To 2) As String
Dim wk As Workbook
Dim i As Integer

RefNum(0) = "100617"
RefNum(1) = "100203"
RefNum(2) = "105522"

Set wk = Workbooks("2004 Hourly data.xlsx")

For i=0 To i=2

Cells.Find(What:=RefNum(i), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

...*code to do with newly found active cell*

Next i

End Sub

The code I had wasn't working at all (scrapped and redone very sloppy) so I am not sure if you can even do what I am trying to do here.
I used a MsgBox to show me how many times it looped and it would always only go through once.
Help would be appreciated.


I have arange of cells in a workbook, i need the vba to find in another workbook.

your help is appreciated

I would like a macro that will search for a value in a range of cells, and jump to each occurance of the value. This is pretty much like what the Find function does. I would like to push a button and each time it finds the next occurance. Preferrably just one button to push, not a "find first" and a different button for "find next".

For example, find occurances of the value "1" in cells L7:L7000 and display the next one each time the macro is executed. If it reaches the end of the range, wrap back up to the top.

I need to search a very large amount of numeric data for two separate numbers. If these two numbers occur in the same row of the data, I need to return the value of the cell in the first column of that row.

I can do the last part (i.e. returning the value), but I'm stuck as to the best way to do the searching, which must be done using VBA.

Could anyone tell me how I can use the find function (I think this is best - if not please let me know) to find these two numbers? Unfortunately they occur in different columns in the data, and can occur on more than one row, so I have to search the whole lot (50000 rows by 28 columns) every time I have a new pair of numbers to find (typically 2000 pairs).

Any help would be very much appreciated.




I have a function I want to create in VBA that would go test by test and provide a value. If first test is true, you get this value, if not go to test 2, if test 2 is true, you get this value, if not go to test 3, etc... For now I am using nested If and I apply the formula to more than 300 cells and it takes too long to load.
Could you please help me improve that function.

     'Define row # of the country
    i = country.Row 
     'Define the column of each test
    col_sanction = Cells.Find(What:="Sanction Level", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column ' Find column # for sanction level
    col_wgi_total = Cells.Find(What:="World Bank Indicators Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column ' Find column # for wgi total
    col_fatf_warning = Cells.Find(What:="FATF or OSFI warning score", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column ' Find column # for FATF warning score
    col_CPI_Index = Cells.Find(What:="High Corruption Perceived Index", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column ' Find column # for CPI Index
     'TEST 1 : Sanctions
    If Cells(i, col_sanction).Value = 3 Then 
        ratingriskv9 = "Sanction Level 3" 
    ElseIf Cells(i, col_sanction).Value = 2 Then 
        ratingriskv9 = "Sanction Level 2" 
    ElseIf Cells(i, col_sanction).Value = 1 Then 
        ratingriskv9 = "Sanction Level 1" 
         'TEST 2 : High Concern (FATF warning >2)
    ElseIf Cells(i, col_fatf_warning).Value > 2 Then 
        ratingriskv9 = "High - Concern" 
         'TEST 3 : High (CPI Index

I'm having trouble when I activate a button-click event. I keep getting a "Runtime error '91'" message. This code searches a sheet for a part number specified through a series of combo boxes then shifts the focus to the cell containing that number. Pretty much the same as the Edit>Find command.
I'm relatively new to VBA, so I'm hoping this will be a quick fix. Any help or advice would be appreciated, thanks.

Function Find_Range(Find_Item As Variant, _ 
    Search_Range As Range, _ 
    Optional LookIn As Variant, _ 
    Optional LookAt As Variant, _ 
    Optional MatchCase As Boolean) As Range 
    Dim c As Range 
    If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
    If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
    If IsMissing(MatchCase) Then MatchCase = False 
    With Search_Range 
        Set c = .Find( _ 
        What:=Find_Item, _ 
        LookIn:=LookIn, _ 
        LookAt:=LookAt, _ 
        SearchOrder:=xlByRows, _ 
        SearchDirection:=xlNext, _ 
        MatchCase:=MatchCase, _ 
        If Not c Is Nothing Then 
            Set Find_Range = c 
            firstAddress = c.Address 
                Set Find_Range = Union(Find_Range, c) 
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing And c.Address  firstAddress 
        End If 
    End With 
End Function 
Private Sub btnFind_Click() 'gets part number from user and calls the Find function and moves the focus to the found range
    Dim PartNumber As Variant 
    PartNumber = CVar(ComboBox1.Text & ComboBox2.Text & ComboBox3.Text) 'gets part number from 3 combo boxes
    Find_Range(PartNumber, Range("A1:A244")).Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hello Oz gents,

Is there a way to use the Find function in a userform? What I am looking for is a code that allows me to type a keyword into a textbox of a userform and search the contents of a listbox in the same userform.

I know it is possible to use the find function in VBA to search a worksheet.
Can the same be done to search a listbox?



Just wondering if anyone had some sample code or links I could check out for a custom Find function. I have created a userform that allows the user to input a search field and an a couple option buttons to help filter the results. The search will look through about 7 different price lists, located on 7 different worksheets. What I want returned is the value and the contents of the cells next to it. I'm basically just having trouble with the find function. I am fairly new to programming, especially with VBA. I've hit a rut and would appreciate any help. Thanks alot.

XL2002 SP3 on XP Pro SP2

In my spreadsheet (one of many) I have a table consisting of dates in column A (in the format dd/mm/yyyy) which represent the first of each month, plus other data in columns B:D.

When the section of my VBA macro reaches the CELLS.FIND line, the active cell is always in the row immediately above the start of my table. The VBA code was originally generated using the macro recorder, so I know that when I used the EDIT/FIND function, the active cell became the cell containing the month I had entered.

In the VBA code I determine the date using the following statement...

rfld = "01/" & Mid("0" & Month(Now()) + 1, Len(Month(Now())), 2) & "/" & Year(Now())

...and use the variable in the cells.find code that follwos ...

Cells.Find(What:=rfld, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Hi all

I'm using the following code (a portion is reproduced here):

Sub MatchupCEFRS()
    Dim matching As Object
    Dim firstmatchaddress As Variant
    Dim counter, i, skip, missingcounter As Integer
    Dim check, filepath, file(2), wname As String
    filepath = "R:Commercial ServicesProcurementClose Outs - Progress Reports"
    file(0) = "FM & P-Package - Progress Report  .xls"
    file(1) = "MPE - Progress Report.xls"
    file(2) = "TF - Progress Report .xls"
    missingcounter = 2
    For i = 0 To 2
        Workbooks.Open filepath & file(i), 0, True
        For Each Sheet In Workbooks(file(i)).Worksheets
            If Sheet.Name = "Reporting" Then
                Exit For
            End If
            counter = 2
                With Workbooks(file(i)).Sheets(Sheet.Name)
                    If .Cells(counter, 23).Value  "N" Then
                        check = .Cells(counter, 3).Value
                        skip = 0
                        Set matching = Range(Cells(2, 2), Cells(6300, 2)).Find(check, LookIn:=xlValues)
                        If Not matching Is Nothing Then
                            If Not matching.Value Like check Then
                                firstmatchaddress = matching.Address
                                    Set matching = Range(Cells(2, 2), Cells(6300, 2)).FindNext(matching)
                                Loop Until matching.Value Like check Or matching.Address = firstmatchaddress
                            End If
                            If matching.Value Like check Then
                                If matching.Offset(0, 7).Value = .Cells(counter, 9).Value Or (matching.Offset(0, 7).Value = 0
And .Cells(counter, 9).Value = "NO REPORT") Then
                                    matching.Offset(0, 11).Value = "Agreed"
                                    matching.Offset(0, 9).Value = Sheet.Name
                                    matching.Offset(0, 10).Value = matching.Offset(0, 10).Value + 1
                                    matching.Offset(0, 11).Value = .Cells(counter, 9).Value
                                End If
                                skip = 1
                            End If
                        End If
and my issue is that the Find function that I am using locates some values and doesn't locate others even when running on the same sheet.

For example, using the above:

The variable 'check' will be set to "FM0082" and I know that "FM0082" is the value at 79,2 on the active worksheet. I can manually type "FM0082" into the ctrl+F find box in the base application and it will definitely locate the cell at 79,2.

However, when the macro runs, this particular cell is not being found but the cells on either side are. Across many thousand entries, the macro is not locating several hundred in this manner.

Has anyone encountered this error or is able to point out what error I am making?

Thanks in advance

Hi, Using Excel 2003 with no VBA.

I am using the Find function in a cell to search for a comma. When it finds
the comma no problem but the function returns the #VALUE if it does not find
the comma. How do I trap for the error?

I am looking at a cell A1 for names and putting the answer in B1. If the
name is "last, first" I switch them to "first last" with a space. If A1
contains "first last" with no comma I am leaving them alone. Cell Function

=IF(FIND(",",A1,1),A1,RIGHT(A1,LEN(A1)-SEARCH(",",A1,1))&" "&

Works with comma but returns #VALUE! with no comma.

How to trap for error?


I have a worksheet that has in times and out times. have created a column to find the difference between out and in with a forumla. However due to sometimes incorrect data entry the out time is lower than the in time giving me a negative timne value displayed by a series of # signs. I now wish to go sequentially down this column that has more than 12,000 entries locating each ########### and correcting the timings. How can i jump from one ######### cell to the immediate next one and so on till the end of the column?

Could not find a way to do it with the find function

I have a Find function used in conjuction with unhiding a sheet, the Find function works, but you have to scroll to find the cell. When you use the "Find" function in Excel it scrolls to the cell and centers it in view, but in the macro it just goes to the cell, but not in view.....any help will be great.

Application.ScreenUpdating = False
Sheets("Sheet1").Visible = True
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate       
Application.ScreenUpdating = True

End Sub

End Sub

No luck finding an answer? You could always try Google.