3

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

pApaAPPApapapa
  • 385
  • 2
  • 4
  • 12
  • Is the reference to `wsRefor` set OK, so that it points to a valid Worksheet object? If you run `MsgBox wsRefor.Name` first in the For-loop, do you get the same error? Btw, there is no If-statement in your example! :) – Olle Sjögren Aug 31 '16 at 10:26
  • Hey @OlleSjögren. Yeah it should be. Also, thanks for pointing out that I wrote "if" and not "for". It is changed now :) – pApaAPPApapapa Aug 31 '16 at 11:08
  • 1
    Related post which is **important to know** while working in VBA - [What does the keyword Set actually do in VBA?](https://stackoverflow.com/q/349613/465053). – RBT Apr 03 '18 at 12:03

2 Answers2

3

You need to use the Set keyword when you are assigning an object rather than a value.

A Range is an object and so it needs to be Set

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

To see the difference, you can do this:

Dim test As Variant

Range("A1").Value = "some text"

test = Range("A1") '// test is now a string containing "some text"

Set test = Range("A1") '// test is now a range object

MsgBox test.Value '// Displays the value of the range object "some text"
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
1

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
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • This worked like a charm. Thanks so much. Increadble how much you gus help in here. When I run the code now, I get the error warning `Run time error '1004' Unable to get the Match propertyof the WorksheetFunction class`, but it still calculates and prints the averages. Any idea why that might be? – pApaAPPApapapa Aug 31 '16 at 11:18
  • @pApaAPPApapapa modify your line to `wsOut.Cells(i + 1, 4).Value = WorksheetFunction.Average(AvgRange)` , you don't need to use the `Application` before the `WorksheetFunction` , should work now – Shai Rado Aug 31 '16 at 11:26
  • Done that. Same happens as before. It does calculate corretly, but still gives me the warning/error message. – pApaAPPApapapa Aug 31 '16 at 11:50
  • @pApaAPPApapapa in you code you are running a loop `For i = 1 To lastCol` , and advancing the rows with this data ? you sure you don't have a Typo ? – Shai Rado Aug 31 '16 at 12:15