Excel vba code using ThisWorkbook.Sheets(1).Range not working, but Sheet1.Range works fine. Why?

Question

When I use wb.Sheets(1).Range("A:A").Find(What:=ID, LookIn:=xlValues) I get error 91 - Object Variable or With Block not set. When I use Sheet1.Range("A:A").Find(What:=ID, LookIn:=xlValues) it returns correct value. Why the difference?

Is there a flowchart I can reference or any simple information available to understand which sub-commands (I don't know the proper word) work with ThisWorkbook and Sheets(#) versus Sheet#.Whatever?

I am hesitant to use Sheet("Name") because names might change later. I am using ThisWorkbook rather than ActiveWorkbook to keep all code attached to the appropriate workbook.

Anything you can offer for simple reference info would be great. I have researched, but still don't understand which sub-commands work with which parent commands. (Again, probably wrong terminology).

Private Sub lstExample_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim wb As Workbook
    Dim I As Integer
    Dim ID As String
    Dim findValue As Range

    Set wb = ThisWorkbook
    'Get the values of the selected row in listbox on doubleclick
    For I = 0 To lstExample.ListCount - 1
        If lstExample.Selected(I) = True Then
    'Set listbox column 1 as ID value to find
            ID = lstExample.List(I, 1)
        End If
    Next I

    'Match ID (column A) on Sheet1
    Set findValue = wb.Sheets(1).Range("A:A").Find(What:=ID, LookIn:=xlValues)
    MsgBox findValue
End Sub

I can't see any reason why that wouldn't work as is. Are you getting the error specifically on that line, or is it on your msgbox line? Because that will bomb in the event that the ID isn't found. Are you certain that there is a sheet with an index of 1 in your workbook (I can't imagine how there wouldn't be, but who knows)? — JNevill
– JNevill, Commented Jun 17, 2015 at 19:20

Dick Kusleika · Accepted Answer · 2015-06-17 19:22:59Z

3

There is no difference between the properties of Sheets(1) and Sheet1 as long as both are the same object - Worksheet Object in your case.

You're getting that error because findValue Is Nothing. That is, it couldn't find the ID in the column. When using the Find method, it's best to specify every argument. Find remembers the last find you did, even if you did it in the UI and your using Find in VBA.

For instance, if you check MatchCase in the UI when you do a Find. Then you do a Find in VBA and don't specify the MatchCase property, it will use whatever you set the last time.

answered Jun 17, 2015 at 19:22

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

keyboardmouse Over a year ago

@JNevill Thanks to Dick Kusleika and JNevill's thoughtful comments, I realized I didn't understand the different between Sheet Index, Sheet Name, and Sheet CodeName. I was incorrectly specifying the index number when using Sheets(1) because I didn't realize it is not a static reference, and can change, being relative to sheet order/positioning. I thought index referred to CodeName, but it does not. Therefore, you both were correct -- findValue was Nothing because it was not searching in the right sheet. Thank you for helping me understand the nuances in sheet names and referencing sheets.

Collectives™ on Stack Overflow

Excel vba code using ThisWorkbook.Sheets(1).Range not working, but Sheet1.Range works fine. Why?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related