Solved

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

Posted on 2010-08-13
17
842 Views
Last Modified: 2013-11-27
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
Comment
Question by:rsaphier
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
17 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33433672
That error with the Find method usually indicates that Excel could not find a cell meeting the search criteria
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33433700
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
 

Author Comment

by:rsaphier
ID: 33433702
The string that it is searching for exits in the excel workbook.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rsaphier
ID: 33433716
In the case where it might not, how would I code for that?
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 33433797
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
 

Author Comment

by:rsaphier
ID: 33433813
Adding that produces Runtime error 424 .. object required.

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33433825
Post the revised procedure, please
0
 

Author Comment

by:rsaphier
ID: 33433878
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33433911
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
 

Author Comment

by:rsaphier
ID: 33433977
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33434052
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
 

Author Comment

by:rsaphier
ID: 33434067
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33434081
Good catch :)
0
 

Author Comment

by:rsaphier
ID: 33434110
Now back to my conditional formatting problem ... can you help me with automating  this?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33434120
Possibly.  Implementing Conditional Formatting via VBA can be a bear, though :)
0
 

Author Comment

by:rsaphier
ID: 33434139
Ok ... should I go back to my other post and close this one out?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33434155
Let's close this out, and try to tackle automating the CF in the other one.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question