How can I use the offset property in VBA to populate a variable in Excel?

[jsbxd9]

Hello all. I am trying to get my code to color cells if a referenced cell is populated. That part works but when I tried to populate another cell a different color I encountered a problem. You can see where the mistake comes in on the code. It is when I try to populate a variable 'strMyVar' with an offset property. I have underlined where I am having the problem. Any suggestions?

Sub colorcells()
Dim x, n, y, i As Double
Dim strMyVal As String
For n = 1 To 3000
Cells(n, 1).Activate
If Cells(n, 1) <> ("") Then
For x = 1 To 7
If x < 7 Then
Cells(n, x).Activate
ActiveCell.Interior.Color = RGB(252, 213, 180)
End If
If x >= 7 Then
Cells(n, x).Activate
strMyVal = ActiveCell.Offset(-3, 0).Value
If strMyVal <> ("") Then
ActiveCell.Interior.Color = RGB(255, 255, 153)
End If
End If
Next x
x = 1
End If
Next n
End Sub

 

[Borg]

Hello all. I am trying to get my code to color cells if a referenced cell is populated. That part works but when I tried to populate another cell a different color I encountered a problem. You can see where the mistake comes in on the code. It is when I try to populate a variable 'strMyVar' with an offset property. I have underlined where I am having the problem. Any suggestions?

Sub colorcells()
Dim x, n, y, i As Double
Dim strMyVal As String
For n = 1 To 3000
Cells(n, 1).Activate
    If Cells(n, 1) <> ("") Then
        For x = 1 To 7
            If x < 7 Then
                Cells(n, x).Activate
                ActiveCell.Interior.Color = RGB(252, 213, 180)
            End If
            If x >= 7 Then
                Cells(n, x).Activate
                [U]strMyVal = ActiveCell.Offset(-3, 0).Value[/U]
                    If strMyVal <> ("") Then
                        ActiveCell.Interior.Color = RGB(255, 255, 153)
                    End If
            End If
        Next x
        x = 1
    End If
Next n
End Sub

I wrapped CODE tags around your code so that it would stay formatted. It's been a long time since I've coded anything in VBA but the first thing that jumps out at me is the question of whether ActiveCell.Offset(-3, 0) is ever null. If so, attempting to retrieve value from a null object will break.

 

[chiro]

Following on from what Borg mentioned above, can you first check that your object being referenced exists?

What is the ActiveCell object structure and what are the valid values for the offset index parameter?

 

[hotvette]

The problem is you are trying to address a cell with a negative row number. If you put a debug print statement in your code like the below, you'll see that the active cell is $G$1 just before the error. The offset operation tries to address a negative row value, which is illegal.

Cells(n, x).Activate
Debug.Print ActiveCell.Address
strMyVal = ActiveCell.Offset(-3, 0).Value

 

[jsbxd9]

Thanks for all the input. Very helpful. I am not a programmer just an engineer trying to make my job a little easier.

Hotvette, how should I reference a cell to the left of an active cell??

 

[hotvette]

For the offset method (should be documented in VBA help), the first parameter is row offset and the 2nd is column offset:

strMyVal = ActiveCell.Offset(0, -1).Value   'cell on immediate left of active cell
strMyVal = ActiveCell.Offset(0, 1).Value    'cell on immediate right of active cell
strMyVal = ActiveCell.Offset(-1, 0).Value   'cell immediately above the active cell
strMyVal = ActiveCell.Offset(1, 0).Value    'cell immediately below the active cell