# Debugging VBA in Excel.

1. Aug 25, 2012

### 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

2. Aug 25, 2012

### Borg

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.

3. Aug 25, 2012

### 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?

4. Aug 25, 2012

### 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.

Code (Text):
Cells(n, x).Activate
strMyVal = ActiveCell.Offset(-3, 0).Value

5. Aug 28, 2012

### 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??

6. Aug 29, 2012

### hotvette

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

Code (Text):
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