Cells.Find() Raises "Runtime Error 91: Object Variable or With Block Not Set"

Question

What I want: I've got a lot of sheets whith different devices. Let's call one of these sheets "WS1".

And I've got a seperate sheet with all existing devices and the appropriate OS next to it. This one we call "list".

Now I want the other sheets (e.g. the "WS1") to check the "list", find the right device, and copy the right OS into the WS1-sheet.

the manual way would be:

• select cell "C3" of WS1 and copy it.
• open the "list"-Sheet and find the copied entry
• select the cell left to the found entry and copy it
• open the WS1 again, select the left cell right next to the active cell and paste the new clipboard (which contains the OS)
• select the next cell which is under and on the right side of the active cell.
• loop until every device in WS1 is filled with an OS

What I've got so far:

Dim DataObj As New MSForms.DataObject
Dim strCliBoa As String
'strCliBoa = DataObj.GetText
DataObj.GetFromClipboard

Range("C3").Select
Selection.Copy
strCliBoa = DataObj.GetText
Sheets("list").Select
Range("A1").Select
Cells.Find(What:=strCliBoa, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Select
Selection.Copy
strCliBoa = DataObj.GetText
Sheets("WS1").Select
ActiveCell.Offset(0, -1).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 1).Select

My issue: "Runtime Error 91: Object variable or with block variable not set" and it marks the cells.find-method.

Can someone tell me what I'm doing wrong?^^ Thanks in advance!

(oh, almost forgot: I'm using ms excel 2010 on Win7)

Answer 1

If the string you're looking for isn't found you'll get that error. The find function returns "Nothing" if nothing is found

    Dim r As Range

    Set r = Cells.find(What:=strCliBoa, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True, SearchFormat:=False)

    If r Is Nothing Then
        'handle error
    Else
        'fill in your code
    End If

Answer 2

I'll provide you an answer using the VLOOKUP() function. So Sheet1 contains several devices and I need to find the correct OS. Sheet2 contains the matching between device and OS.

On Sheet1 enter this formula in the cell next to device and pull it down (of course edit to your specific needs).

=VLOOKUP(A2;Sheet2!$A$1:$B$20;2;0)

EDIT: the VLOOKUP function will only work if the OS is in second column. Either switch around the columns or use a helper column at the end to contain the OS.