Solved

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

Posted on 2010-08-13
17
831 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 92

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

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

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 92

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 92

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 92

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 92

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 92

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 92

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

739 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