CODEWITHSUNDEEP
excelvbavariables
grNadpa
grNadpa

Reputation: 79

Variable type for cell

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.

Upvotes: 0

Views: 99

Answers (2)

TinMan
TinMan

Reputation: 7759

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

Upvotes: 3

Glenn G
Glenn G

Reputation: 667

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)

Upvotes: 3

Related Questions