Help VBA test text/string using .Find error 91 object variable or with block variable required?

[Joyner]

Hello,

I am trying to test several column headings on a sheet to see if they match the expected text strings for each cell. The match does not have to be real strict as in all upper and lower case must match, I think a simple test that all of the characters match would be okay. After the test I want to set a Boolean as True

The headings/text row can change based on the output of the sheet so I am trying to use the .Find and Offset to locate the subject headings/text to test. Since the text/string to test will always to in a known offset from a known text in a cell in column “A” I have tried to use the follow method to do the test. All of the tests must be true for the Boolean to be set to true:

If Columns(1).Find("Subtotal").Offset(-1, 1).Value = "High Dollar Amount" And _
Columns(1).Find("Subtotal “).Offset(-1, 2).Value = "Sampling Amount" And _
Columns(1).Find("Subtotal").Offset(-1, 3).Value = "Combined" Then
sTest_Sheet = True
End If

I get an error 91 object variable or with block variable required.

I have researched and cannot figure this out. I have used similar statements in my code and it is okay as in:

x = Columns(1).Find("Subtotal").Offset(0, 1).Value etc. and this returns the expected value with nothing needing to be set, or defined etc. In those cases though, it is just returning the value. Based on what I have found, believe if any of the tests are false it errors out.

I have tried InStr and StrComp with the same results.

Any ideas how what I am doing wrong or how I can get this to work?

Thank you for any assistance.

 

[John_w]

"I get an error 91 object variable or with block variable required."

Because the .Find("Subtotal “) (notice the space and the odd double quote character) is not being found. Even with this corrected to .Find("Subtotal") it would fail with the same error if "Subtotal" isn't found.

Easier to Set and test separately, like this:

    Dim subtotal As Range
    Dim sTest_Sheet As Boolean
    
    Set subtotal = Columns(1).Find("Subtotal", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    If Not subtotal Is Nothing Then
        If subtotal.Offset(-1, 1).Value = "High Dollar Amount" And _
            subtotal.Offset(-1, 2).Value = "Sampling Amount" And _
            subtotal.Offset(-1, 3).Value = "Combined" Then
            sTest_Sheet = True
        End If
    End If

 

[Joyner]

Thanks John w

That works great, I'll give it a bit more testing, but seems to do exactly what I wanted.

At first I was getting a Type Mismatch Error, then I removed this part:

After:=ActiveCell

That was causing the error if the active cell wasn't in column "A", I think all is fine after removing it.

Thanks again, saved me hours of struggling with this