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

Your code works at my side. Could you share us a test workbook to debug your code?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Answer 4

7DC07J

Not found？

Answer 5

Hi,

I created a sample file but I ran into troubles trying to share it. I'm not sure my work allows sharing externally. But in the process I made a change, which resulted in the code working, but I'm still not sure why it wasn't working before. And it's not a fix for me as I need the file set up in a certain way.

To elaborate:

The VBA code that refers to S1:Z1 in the Lookups tab is referencing a named range (CodesInUse) that's spilled horizontally as headings. When I pasted over that formula as values, the macro worked. So, for some reason, the VBA code doesn't like that I'm referencing an array (or maybe that I'm referencing a named range?).

The named range is in another tab, which isn't defined in the macro.

Any idea why this would be the case. I'm pretty new to VBA, and figuring it out as I go along, so I'm pretty clueless on this one.

Thanks