Debugging VBA in Excel.

  • Thread starter jsbxd9
  • Start date
  • #1
6
0

Main Question or Discussion Point

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
 

Answers and Replies

  • #2
Borg
Science Advisor
Gold Member
1,874
2,301
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?

Code:
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.
 
  • #3
chiro
Science Advisor
4,790
131
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
hotvette
Homework Helper
996
5
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:
Cells(n, x).Activate
Debug.Print ActiveCell.Address
strMyVal = ActiveCell.Offset(-3, 0).Value
 
  • #5
6
0
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
hotvette
Homework Helper
996
5
For the offset method (should be documented in VBA help), the first parameter is row offset and the 2nd is column offset:

Code:
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
 

Related Threads on Debugging VBA in Excel.

  • Last Post
Replies
18
Views
4K
Replies
4
Views
5K
  • Last Post
Replies
1
Views
2K
  • Last Post
Replies
8
Views
7K
Replies
2
Views
3K
Replies
1
Views
5K
Replies
2
Views
2K
Replies
1
Views
54K
Replies
7
Views
29K
  • Last Post
Replies
1
Views
510
Top