Solved

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

Posted on 2010-08-13
17
802 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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

896 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

11 Experts available now in Live!

Get 1:1 Help Now