'For Loop VBA - Runtime error'91': Object variable or With block variable not set

So I am trying to just rearrange some messy data on another sheet and I can't figure out why I keep getting a "Runtime error'91': Object variable or With block variable not set" when i hit the for i = 2 to 73 line. Thoughts?

  Sub dostuff()

Dim i As Integer
Dim j As Integer
Dim sht1 As Worksheet
i = 2
sht1 = Worksheets("Sheet1")

For i = 2 To 73
    Cells(i, 2).Copy Destination = sht1.Cells(i, 1)
    Cells(i, 5).Copy Destination = sht1.Cells(i, 2)
    j = 1
    For j = 1 To Range("Y" & i).Value2
        Worksheets("Time Log").Cells(i, ((j * 4) + 2)).Copy Destination = sht1.Cells(i, 3)
        Worksheets("Time Log").Cells(i, ((j * 4) + 3)).Copy Destination = sht1.Cells(i, 4)
        Worksheets("Time Log").Cells(i, ((j * 4) + 5)).Copy Destination = sht1.Cells(i, 5)
    Next j
Next i

End Sub
excelvbafor-loop


Solution 1:[1]

Error 91 is asking you to create a with block:

Sub dostuff()

Dim i%, j%, endLoop%
Dim sht1 As Worksheet
'i = 2 not necessary

set sht1 = Worksheets("Sheet1") 'set was missing

With sht1 'with block
    For i = 2 To 73
        Cells(i, 2).Copy .Cells(i, 1)
        Cells(i, 5).Copy .Cells(i, 2)
        j = 1 'not necessary
        endLoop = Range("Y" & i).Value2 'so you don't have to evaluate it each iteration
        For j = 1 To endLoop
            Worksheets("Time Log").Cells(i, ((j * 4) + 2)).Copy .Cells(i, 3)
            Worksheets("Time Log").Cells(i, ((j * 4) + 3)).Copy .Cells(i, 4)
            Worksheets("Time Log").Cells(i, ((j * 4) + 5)).Copy .Cells(i, 5)
        Next j
    Next i
End with 'close with block

End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 LletDeCabra

Related Questions