Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 861
  • Last Modified:

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

0
rsaphier
Asked:
rsaphier
  • 8
  • 8
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now