CODEWITHSUNDEEP
vbaexcel
kmalik
kmalik

Reputation: 17

Object variable not set

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

Upvotes: 0

Views: 275

Answers (1)

A.S.H
A.S.H

Reputation: 29352

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)

Upvotes: 3

Related Questions