Basic VBA code generating Run-time error '91': Object variable or With block variable not set

Question

I'm having some issues with a code generating a 'Run-time error '91': Object variable or With block variable not set' message.

I created a new sub to simplify and test only the offending lines, and it's still generating the error. I'm really struggling to figure out what the issue is here.

I've used this same line of code (with different range and find criteria) elsewhere and it has worked fine.

Sub TESTING()

Dim ws1 As Worksheet, ws2 As Worksheet 

Set ws1 = ThisWorkbook.Worksheets(Sheet2.Name) 'Cost Tracker tab 

Set ws2 = ThisWorkbook.Worksheets(Sheet8.Name) 'Lookups tab 

ws2.Activate 

Dim Test1 As Long 

Test1 = ws2.Range("S1:Z1").Find("7DC07J").Column   << ERROR HERE

End Sub

Thanks

Answer 1

Test1 = ws2.Range("S1:Z1").Find(What:="ABC123", LookIn:=xlValues).Column-18

Answer 2

You may try this one.

=====

Sub TESTING1()

Dim ws2 As Worksheet, ws3 As Worksheet 

Set ws2 = ThisWorkbook.Worksheets(Sheet8.Name) 'Lookups tab 

ws2.Activate 

For i = 19 To 26

If Cells(1, i) = "ABC123" Then

test = i

End If

Next i

End Sub

=====

OR

=========================

Sub TESTING1()

Dim ws2 As Worksheet, ws3 As Worksheet 

Set ws2 = ThisWorkbook.Worksheets(Sheet8.Name) 'Lookups tab 

ws2.Activate 

Dim Test1 As Long 

Test1 = ws2.Range("S1:Z1").Find(What:="ABC123", LookIn:=xlValues).Column 

End Sub

=======================

Answer 3

Thank you, that worked! For the second option, is there a way to have the column result return relative to the start of the range? Ie. equal to 1 rather than 19? Or do I need to do the For loop as in the previous option?

Thanks

Answer 4

You may upload file in private message.

Answer 5

Thank you - I just tried this and unfortunately the file failed to upload. Not having much luck with this!

To give a bit more detail, in the sample file I have the Setup tab, and the Lookups tab.

Setup tab has a named range called CodesInUse, and the Lookups tab transposes this range in a spilled array formula (I posted an image of this before).

When I reference the array formula in the lookup tab, I get the runtime error when I run the VBA code. If I replace the formula as values, the code runs fine. If I update the code to refence the actual named range in the Setup tab, the code works.

As an aside, I also want to return the column number relative to the start of the range, not to the first column of the sheet. Is there a simple way to do this?

TESTING1 generates runtime error, TESTING2 works.