CODEWITHSUNDEEP
vbaexcelobject
keyboardmouse
keyboardmouse

Reputation: 125

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

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

Upvotes: 1

Views: 6610

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

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.

Upvotes: 3

Related Questions