How to fix Error 91 Object Variable or with block variable not set?

Question

I am trying to call a function I created and I keep receiving this error.

'This is where I try and call the function
 Sub Test1()
 Dim ChartCells1 As Range
 Set ChartCells1 = GetValues(ActiveSheet.Range("BV2", Range("BV2").End(xlDown)), ActiveSheet.Range("BV2"))
 ChartCells1.Select
 End Sub

'this is the function  am trying to call   
Function GetValues(Column As Range, Value As Range) As Range

Dim ChartCells As Range
Dim Count As Range
Dim Cells As Range
Dim Number As Range

DataSheetArea1Zone16.Activate

Set Number = Range(Value)
Set Cells = Range(Column)
Set ChartCells = Range(Value).Offset(0, -36)

For Each Count In Cells

  If Count.Value <> Number Then
    Set ChartCells = Union(ChartCells, Count.Offset(0, -36))
    Set Number = Count

  End If

Next Count

GetValues = ChartCells

End Function

I keep receiving the error 91 on the line GetValues = ChartCells or on the line ChartCells1.Select

`Value As Range` "Value/Cells" is a reserved word. To avoid complications, please do not use it as a variable. — Siddharth Rout, Jul 16 '19 at 12:07

score 0 · Answer 1 · answered Jul 16 '19 at 12:20

In the code, the following were problematic:

Set Number = Range(Value)
Set Cells = Range(Column)
Set ChartCells = Range(Value).Offset(0, -36)
'----
GetValues = ChartCells

As Value and Column were already declared as ranges, the Range(Value) was causing the error. The function returns type Range thus it should be set, e.g. GetValues = ChartCells.

In general, working with Activate and Select in VBA is not a best practice - How to avoid using Select in Excel VBA

This works (but it is a good idea to refactor the ActiveSheet and .Activate away):

 Sub Main()
    Dim ChartCells1 As Range
    Set ChartCells1 = GetValues(ActiveSheet.Range("BV2", Range("BV2").End(xlDown)), ActiveSheet.Range("BV2"))
    ChartCells1.Select
 End Sub

'this is the function  am trying to call
Function GetValues(Column As Range, Value As Range) As Range

    Dim ChartCells As Range
    Dim Count As Range
    Dim Cells As Range
    Dim Number As Range

    Worksheets(1).Activate

    Set Number = Value
    Set Cells = Column
    Set ChartCells = Value.Offset(0, -36)

    For Each Count In Cells
        If Count.Value <> Number Then
            Set ChartCells = Union(ChartCells, Count.Offset(0, -36))
            Set Number = Count
        End If
    Next Count

    Set GetValues = ChartCells

End Function

How to fix Error 91 Object Variable or with block variable not set?

1 Answers1