RunTime Error 91 - Object Variable or with block variable not set

When trying to execute the attached code I'm receiving a runtime error when it hits  
".cells.find("NoMatchTotal ..."  

There may be more errors but this is currently where I'm stuck.

Any suggestions?
Dim qdf As QueryDef
        
For Each qdf In CurrentDb.QueryDefs
    If Left(qdf.Name, 1) = "~" Then
    Else
        strName = qdf.Name
        
        Select Case Left(strName, 4)
            Case Is = "qry_"
                strfile = Mid(strName, 5, 99)
            Case Else
                strfile = strName
        End Select
        
     strPath = "\\bhihnbwfa01\Groups\Projects\SAP_Validation\PhaseFinal\Spreadsheets\SPCritical\"
        strNewFile = strPath & strfile & ".xlsx"
        DoCmd.OutputTo acOutputQuery, strName, acFormatXLSX, strNewFile, False
        
        strOutput = strPath & strfile & ".xlsx"
    
    'prelim cleanup
        Dim exapp As Excel.Application
        Dim exbook As Excel.Workbook
        Set exapp = Excel.Application
        exapp.DisplayAlerts = False
        Set exbook = exapp.Workbooks.Open(strOutput)
    
        exapp.Visible = True 'set back to false
        exapp.Interactive = True
    
        With exapp
            .Cells.Select
            .Selection.ColumnWidth = 88.29
            .Cells.EntireRow.AutoFit
            .Cells.EntireColumn.AutoFit
            .Rows("1:1").Select
            .Selection.AutoFilter
            
            .Range("A1").Select
            .Cells.Find("NoMatchTotal", .ActiveCell, xlFormulas, xlPart, xlByColumns, xlNext, False, False).Activate
            
            .ActiveCell.Offset(0, -1).Range("A1").Select
            .Range(.Selection, .Selection.End(xlDown)).Select
            .Range(.Selection, .Cells(1)).Select
            With .Selection.Interior
            .PatternColor = 12632256
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.149998474074526
            .PatternTintAndShade = 0
            End With
            .Range("A1").Select
            .Cells.Find("NoMatchTotal", ActiveCell, xlFormulas, xlPart, xlByColumns, xlNext, False, SearchFormat:=False).Activate
            .Selection.Copy
            .Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .Application.CutCopyMode = False
            .Range(Selection, Selection.End(xlToLeft)).Select
             .ActiveWorkbook.SaveAs strOutput, xlOpenXMLWorkbook, , , , False
            .Application.Quit
        End With

    End If
    
MoveOn:
Next qdf
  
  MsgBox ("Export and Preliminary Spreadsheet Formatting Completed!")
  Set qdf = Nothing

Open in new window

rsaphierAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
That error with the Find method usually indicates that Excel could not find a cell meeting the search criteria
0
Rey Obrero (Capricorn1)Commented:
try this revision, add the microsoft DAO x.x object library to your references

Dim qdf As dao.QueryDef, db as DAO.database

set db=currentdb
       
For Each qdf In db.QueryDefs
0
rsaphierAuthor Commented:
The string that it is searching for exits in the excel workbook.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rsaphierAuthor Commented:
In the case where it might not, how would I code for that?
0
Patrick MatthewsCommented:
Assuming inside that With block...    If .CountIf(.ActiveSheet.[1:1], "NoMatchTotal") > 0 Then        'code if it's there    Else        'code if it's not    End If
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rsaphierAuthor Commented:
Adding that produces Runtime error 424 .. object required.

0
Patrick MatthewsCommented:
Post the revised procedure, please
0
rsaphierAuthor Commented:
I'm on a really tight deadline to resolve this and truly appreciate your help!!!
Dim qdf As dao.QueryDef
Dim db As dao.Database

Set db = CurrentDb

        
For Each qdf In db.QueryDefs
    If Left(qdf.Name, 1) = "~" Then
    Else
        strName = qdf.Name
        
        Select Case Left(strName, 4)
            Case Is = "qry_"
                strfile = Mid(strName, 5, 99)
            Case Else
                strfile = strName
        End Select

        
        strPath = "\\bhihnbwfa01\Groups\Projects\SAP_Validation\PhaseFinal\Spreadsheets\SPCritical\"
        strNewFile = strPath & strfile & ".xlsx"
        DoCmd.OutputTo acOutputQuery, strName, acFormatXLSX, strNewFile, False
        
        strOutput = strPath & strfile & ".xlsx"
    
    'prelim cleanup
        Dim exapp As Excel.Application
        Dim exbook As Excel.Workbook
        Set exapp = Excel.Application
        exapp.DisplayAlerts = False
        Set exbook = exapp.Workbooks.Open(strOutput)
    
        exapp.Visible = True 'set back to false
        exapp.Interactive = True
    
        With exapp
            .Cells.Select
            .Selection.ColumnWidth = 88.29
            .Cells.EntireRow.AutoFit
            .Cells.EntireColumn.AutoFit
            .Rows("1:1").Select
            .Selection.AutoFilter
            .Range("A1").Select
            
            If .CountIf(.ActiveSheet.[1:1], "NoMatchTotal") > 0 Then
                .Cells.Find("NoMatchTotal", .ActiveCell, , , , xlNext, False, False).Activate = ""
                .ActiveCell.Offset(0, -1).Range("A1").Select
                .Range(.Selection, .Selection.End(xlDown)).Select
                .Range(.Selection, .Cells(1)).Select
                With .Selection.Interior
                .PatternColor = 12632256
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.149998474074526
                .PatternTintAndShade = 0
                End With
            Else
            End If
            
            .ActiveWorkbook.SaveAs strOutput, xlOpenXMLWorkbook, , , , False
            .Application.Quit
        End With
     End If
    
Next qdf

  MsgBox ("Export and Preliminary Spreadsheet Formatting Completed!")
  Set qdf = Nothing
  Set db = Nothing

Open in new window

0
Patrick MatthewsCommented:
OK, let's come at it from a different angle.  In the block below, describe in sentences exactly what you are trying to do.
            If .CountIf(.ActiveSheet.[1:1], "NoMatchTotal") > 0 Then
                .Cells.Find("NoMatchTotal", .ActiveCell, , , , xlNext, False, False).Activate = ""
                .ActiveCell.Offset(0, -1).Range("A1").Select
                .Range(.Selection, .Selection.End(xlDown)).Select
                .Range(.Selection, .Cells(1)).Select
                With .Selection.Interior
                .PatternColor = 12632256
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.149998474074526
                .PatternTintAndShade = 0
                End With
            Else
            End If

Open in new window

0
rsaphierAuthor Commented:
I am searching for the string "NoMatchTotal" in Row 1.  

If it exists, select all colums to the left of that column and use a fill color of gray
0
Patrick MatthewsCommented:
Then try:
            If .CountIf(.ActiveSheet.[1:1], "NoMatchTotal") > 0 Then
                With .Cells(1, 1).Resize(1, .Match("NoMatchTotal", .ActiveSheet.[1:1], 0) - 1).EntireColumn.Interior
                    .PatternColor = 12632256
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.149998474074526
                    .PatternTintAndShade = 0
                End With
            Else
            End If

Open in new window

0
rsaphierAuthor Commented:
ok ... I figured out the problem

.Cells.Find("NoMatchTotal", .ActiveCell, , , , xlNext, False, False).Activate = ""

should be

.Cells.Find("NoMatchTotal", .ActiveCell, , , , xlNext, False, False).Activate




0
Patrick MatthewsCommented:
Good catch :)
0
rsaphierAuthor Commented:
Now back to my conditional formatting problem ... can you help me with automating  this?
0
Patrick MatthewsCommented:
Possibly.  Implementing Conditional Formatting via VBA can be a bear, though :)
0
rsaphierAuthor Commented:
Ok ... should I go back to my other post and close this one out?
0
Patrick MatthewsCommented:
Let's close this out, and try to tackle automating the CF in the other one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.