Solved

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

Posted on 2010-08-13
17
797 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 119

Expert Comment

by:Rey Obrero
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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now