Variable type for cell

Question

I've tried Dim CellA As Long, As Integer and As Range trying to save the address of a cell. In this example, (using Range) I get the

Run Time Error 91 Object variable or with block variable not set

on line 4: CellA = Cells(ActiveCell.Row - 1, 1)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ColA As String
Dim ColB As String
Dim CellA As Range

CellA = Cells(ActiveCell.Row - 1, 1)
If (Len(Cells(ActiveCell.Row - 1, 1) > 0)) Then
    ColA = CleanCode(Cells(ActiveCell.Row - 1, 1))
    ColB = CleanCode(Cells(ActiveCell.Row - 1, 2))
    If (ColA <> ColB) Then
        MsgBox (Cells(ActiveCell.Row - 1, 2))
        Range(Cells(ActiveCell.Row - 1, 1)).Select
    End If
End If

End Sub

I would like to replace all my Cells(ActiveCell.Row...) references with variable references including my range select (which obviously doesn't work as it stands).

FYI: This routine generates a message box with the contents of a (hidden) column B cell when what I type in a column A cell doesn't match that column B cell. I wrote it to help me memorize my lines for my video head-shots.

The error message is pretty clear - you have an object variable that is not `Set`. — BigBen, Nov 21 '19 at 17:32
Agreed. I understand the argument was asking for a different type of variable. I took the word object as a generic term not a variable type. NO excuse. — grNadpa, Nov 21 '19 at 17:51

score 3 · Answer 1 · answered Nov 21 '19 at 17:29

3

Because CellA is an object you can't do this

CellA = Cells(ActiveCell.Row - 1, 1)

you have to use the Set keyword like this

 Set CellA = Cells(ActiveCell.Row - 1, 1)

answered Nov 21 '19 at 17:29

Glenn G

667
10
24

Thank you Glenn G. That took care of it. Did not think about it being an object. I still have the issue of trying to position the cursor back to the offending cell. I gather either the Range command is not the right one or I need a different type argument. – grNadpa Nov 21 '19 at 17:49
As for why you're getting that specific error, see [this answer](https://stackoverflow.com/a/58734975/1188513). Or [this one](https://stackoverflow.com/a/58844925/1188513) – Mathieu Guindon Nov 21 '19 at 17:57
@MathieuGuindon. I see. Thank you for your response. I replaced the range statement with ActiveCell.Offset(-1, 0).Activate. Is that the proper solution? – grNadpa Nov 21 '19 at 18:14
1

It has the enormous advantage of being explicit about what's going on. Not ideal that it uses `Activate`, but at least it's explicit about it. See [how to avoid select & activate](https://stackoverflow.com/q/10714251/1188513) – Mathieu Guindon Nov 21 '19 at 18:20

score 3 · Answer 2 · answered Nov 21 '19 at 18:08

Ranges are objects. You need to use the keyword Set to make an object assignment.

Set CellA = Cells(ActiveCell.Row - 1, 1)

CellA is never used
These extra parenthesis (Len(Cells(ActiveCell.Row - 1, 1) > 0)) are just clutter.
Avoid using parenthesis like this:MsgBox (Cells(ActiveCell.Row - 1, 2)) could cause issues.

Replace Range(Cells(ActiveCell.Row - 1, 1)) with ActiveCell.Offset(-1,2) or ActiveCell(-1,2)

Private Sub Worksheet_Change(ByVal Target As Range) Dim ColA As String Dim ColB As String Dim CellA As Range Dim CellB As Range

Set CellA = ActiveCell.Offset(-1, 1)
Set CellB = ActiveCell.Offset(-1, 2)

If Len(CellA.Value) > 0 Then
    ColA = CleanCode(CellA)
    ColB = CleanCode(CellB)

    If (ColA <> ColB) Then
        MsgBox CellB
        ColA.Select
    End If
End If

End Sub

I did need to change a couple of things. I changed the offsets from -1,1 to -1,0 and from -1,2 to -1,1. And the ColA.Select to CellA.Select. It works exactly as I need. Thanks. — grNadpa, Nov 21 '19 at 18:31

Variable type for cell

2 Answers2