VBA Error Message: Object Variable or With Block Variable not Set. Can't See It

Question

Line 22 is throwing the error (Set wbPath2)

This code is supposed to loop through each worksheet in my workbook and, as it loops, open another workbook related to the current loop iteration, then sum a column, then put that SUM in my original workbook. I'm getting and object error 91. I've been scratching my head for a while. Anyone know why this error message appears?

Private Sub PopulateData_Click()
Application.ScreenUpdating = False

Dim ws As Worksheet
Dim lastDay As Long

lastDay = Day(WorksheetFunction.EoMonth(ComboBox1.Value & Year(Date), 0))
monthNumber = Month(DateValue("01-" & ComboBox1.Value & "-1900")) 
Root = "C:\myDirectory\" & Year(Date) & "\" & 
        monthNumber & ". " & ComboBox1.Value & " " & Year(Date) & "\"

'TOTAL CARS PER WEEK
Dim wbPath2 As Object
sourceFile = monthNumber & ". " & ComboBox1.Value & " " & Year(Date)
sourceSheet = "\[" & ws.Name & " " & monthNumber & "." & lastDay & "." & 
               Format(Now(), "yy") & ".csv]"


For Each ws In ThisWorkbook.Sheets
    If (ws.Name <> "Master") And (ws.Name <> "Combined") Then
        Set wbPath2 = Workbooks.Open(Root & ws.Name & " " & monthNumber & 
                      "." & lastDay & "." & Format(Now(), "yy") & ".csv")
        With ws
            .Cells(Application.WorksheetFunction.Match("Total cars per 
            week", Range("A:A"), 0), 18).Formula = "=SUM('" & Root & 
            sourceFile & sourceSheet & ws.Name & " " & monthNumber & "." & 
            lastDay & "." & Format(Now(), "yy") & "'!$H:$H)"
        End With

        wbPath2.Close
        MsgBox wbPath2
    End If
Next

Application.ScreenUpdating = True
End Sub

I would have expected it to occur on the line before that, where you are using ws.Name without having set ws to anything. — YowE3K
– YowE3K, Commented Jun 30, 2017 at 19:33
Note that the Sheets collection contains Worksheet objects, but also Chart objects, which will make your code throw another type of error if your workbook contains any. If you want to iterate worksheets, iterate the Worksheets collection. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 30, 2017 at 20:00
You also have unqualified Range calls inside that With block - these are implicitly referring to whatever worksheet is currently active, and that's yet another bug / runtime error 1004 waiting to be thrown. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 30, 2017 at 20:01

Mathieu Guindon · Accepted Answer · 2017-06-30 20:29:30Z

3

I had to Set the ws object to resolve run time 91 error. Look in the comments section for Mat's Mug's additional bug fixes.

Private Sub PopulateData_Click()

    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Activesheet

    '...

edited Jun 30, 2017 at 20:29

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Jun 30, 2017 at 20:20

Sean Kelly

1833 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YowE3K Over a year ago

I still can't see how that would have fixed an error at the Set wbPath2 statement, which is where the question says the code crashed, because by the time it gets to that line ws is already set by the For Each ws In ThisWorkbook.Sheets line. I still think your code must be crashing earlier, at the line using ws.Name before the loop starts.

Collectives™ on Stack Overflow

VBA Error Message: Object Variable or With Block Variable not Set. Can't See It

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related