Object variable not set

Question

I keep on getting an "object variable not set" error with this code. I want to offset the startcell cell address to take the average of a corresponding column. However, vba tries to offset the value of the startcell, rather than the cell address. It would be great if someone can offer me some guidance. Thanks!

Sub Macro1()

    Dim i As Integer
    Dim rowcount As Integer
    Dim startcell As Range

    startcell = ActiveSheet.Cells(2, 3).Address(False, False)
    rowcount = Range("C2", Range("C2").End(xlDown)).Count

    For i = 1 To rowcount
        If Not Cells(i, 3).Value = Cells(i + 1, 3).Value Then
            startcell.Offset(0, 11).Value = Application.WorksheetFunction.Average( _
            Range(startcell.Offset(0, 8), Cells(i, 11)))
            startcell = ActiveSheet.Cells(i + 1, 3).Address(False, False)
        End If
    Next i

End Sub

use Set for the range variable startcell. Set startcell = ActiveSheet.Cells(2, 3) — cyboashu
– cyboashu, Commented Jan 31, 2017 at 16:44
use Set startcell = ActiveSheet.Range(Cells(2, 3), Cells(2, 3)) — Shai Rado
– Shai Rado, Commented Jan 31, 2017 at 17:07

A.S.H · Accepted Answer · 2017-01-31 17:36:11Z

3

To save a given range in a variable, you either use its address, which is a string, or a Range object. The latter is usually preferred, and it looks like this was you intent.

Dim startcell As Range
....
Set startcell = ActiveSheet.Cells(2, 3) ' <-- Set a range object variable
....
Set startcell = ActiveSheet.Cells(i + 1, 3) ' <-- Set

On the other hand if you want to use the address (less recommended unless there are specific reasons):

Dim startAddr As String ' <--
....
startAddr = ActiveSheet.Cells(2, 3).Address(False, False) ' <-- ok, save address
....
Range(startAddr).Offset(0, 11).Value = ... ' Range(startAddr) reconstructs a range from the address
startAddr = ActiveSheet.Cells(i + 1, 3).Address(False, False)

edited Jan 31, 2017 at 17:36

answered Jan 31, 2017 at 17:08

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

Object variable not set

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related