"Object variable or With block variable not set" in VBA

pApaAPPApapapa

I am looking to calculate a column (in wsOut) of averages using VBA. The input is in another sheet (wsRefor).

I use the following code, where I use the worksheet function to calculate the average

Dim Avg As Double
Dim AvgRange As Range
Set Reformulering = ActiveSheet

For i = 1 To lastCol
    AvgRange = Range(wsRefor.Cells(1 + i, 4), wsRefor.Cells(1 + i, lastCol))
    wsOut.Cells(i + 1, 4).Value = Application.WorksheetFunction.Average(AvgRange)
Next

Yet, I get the mistake, from the second line inside of the for-loop:

"Object variable or With block variable not set"

I am not sure I understand the error from videos I have watched and other forum discussion, so I am hoping anyone can explain or potentially point of the mistake

Shai Rado

Assuming you defined Dim wsRefor As Worksheet, and set it to the right Sheet, then modify your line:

AvgRange = Range(wsRefor.Cells(1 + i, 4), wsRefor.Cells(1 + i, lastCol))

to:

Set AvgRange = wsRefor.Range(Cells(1 + i, 4), Cells(1 + i, lastCol))

or, on the safe side:

With wsRefor
     Set AvgRange = .Range(.Cells(1 + i, 4), .Cells(1 + i, lastCol))
End With

Edit 1: full code which I've tested (also has error handling for the Average Function)

Option Explicit

Sub DynamicAvgRange()

Dim wsRefor             As Worksheet
Dim wsOut               As Worksheet
Dim Avg                 As Double
Dim AvgRange            As Range
Dim lastCol             As Long
Dim i                   As Long

Set wsRefor = ThisWorkbook.Sheets("Refor")
Set wsOut = ThisWorkbook.Sheets("Out")

' just for simulating the tests
lastCol = 6

For i = 1 To lastCol
    With wsRefor
        Set AvgRange = .Range(.Cells(1 + i, 4), .Cells(1 + i, lastCol))
    End With
    If Not IsError(Application.Average(AvgRange)) Then
        wsOut.Cells(i + 1, 4).Value = Application.Average(AvgRange)
    Else
        ' Average value returned an error (no values in the searched range)
        wsOut.Cells(i + 1, 4).Value = "" ' put a blank value >> modify to your needs
    End If
Next i

End Sub

Collected from the Internet

Please contact [email protected] to delete if infringement.