Solved

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

Posted on 2010-08-13
17
811 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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