Debug.Print Causes Crash

Joozh · May 18th 2007

Hi,

Continuing with my goal to learn VBA and while playing with the following code, I encountered strange behavior of debug statements. In the code below I have put my comments to identify where the "funny behavior" is encountered.

Will appreciate if you can help me understand. I also have a simple request/question at the end of the code below:

Code

Sub Test()


Dim Rng1 As Range
Dim Rng2 As Range


Set Rng1 = Worksheets("Sheet1").Cells(1, 1)
irow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Set Rng1 = Rng1.Resize(irow - Rng1.Row + 1, 1)


Set Rng2 = Worksheets("Sheet2").Cells(1, 1)
irow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Set Rng2 = Rng2.Resize(irow - Rng2.Row + 1, 1)




For Each r1 In Rng1
  r1.Interior.ColorIndex = 6
  For Each r2 In Rng2
    If r2.Value = r1.Value Then
      r1.Interior.ColorIndex = xlColorIndexNone
      Exit For
    End If
  Next r2
Next r1


Debug.Print "R1.Value = " & r1.Value
Debug.Print "R1.Address = " & r1.Address
[COLOR="SeaGreen"][B]' Both of the above statements crash the code but the next 2 lines of code do not.
' How come the next 2 lines of code do not crash???[/B][/COLOR]
Debug.Print "R2.Value = " & r2.Value
Debug.Print "R2.Address = " & r2.Address




For Each r2 In Rng2
  r2.Interior.ColorIndex = 6
  For Each r1 In Rng1
    If r2.Value = r1.Value Then
      r2.Interior.ColorIndex = xlColorIndexNone
      Exit For
    End If
  Next r1
Next r2


[B][COLOR="seagreen"]' And to make things even more interesting, all of the following 4 lines of code crash???[/COLOR][/B]
Debug.Print "R1.Value = " & r1.Value
Debug.Print "R1.Address = " & r1.Address
Debug.Print "R2.Value = " & r2.Value
Debug.Print "R2.Address = " & r2.Address


End Sub

Display More

Also, what i want to do is that at the end, the very next cell (i.e. 1 row down) of Rng1 and Rng2 should be filled with heavy border and Red fill.

Thanks.

filo65 · May 18th 2007

Re: Weird Behavior Of Debug Statements

a possibility could be:

in the first case

Code

For Each r1 In Rng1
  r1.Interior.ColorIndex = 6
  For Each r2 In Rng2
' this condition is true: you exit the first loop assigning a value to r2 
    If r2.Value = r1.Value Then
      r1.Interior.ColorIndex = xlColorIndexNone
      Exit For
    End If
  Next r2
Next r1
'r1 is out of range

Display More

Code

For Each r2 In Rng2
  r2.Interior.ColorIndex = 6
  For Each r1 In Rng1
' the condition is always false
    If r2.Value = r1.Value Then
      r2.Interior.ColorIndex = xlColorIndexNone
      Exit For
    End If
  Next r1
Next r2
'both "r"s are out of range

Display More

filippo

**Andy Pope** · May 18th 2007

Re: Weird Behavior Of Debug Statements

the objects r1 and r2 are loop objects. Once the loop is complete they reference nothing.

If you are try to find the last cell in the ranges Rng1 and Rg2 use

Code

Debug.Print "R1.Value = " & Rng1.Cells(Rng1.Rows.Count, 1).Value
Debug.Print "R1.Address = " & Rng1.Cells(Rng1.Rows.Count, 1).Address
Debug.Print "R2.Value = " & Rng2.Cells(Rng2.Rows.Count, 1).Value
Debug.Print "R2.Address = " & Rng2.Cells(Rng2.Rows.Count, 1).Address

You might find using some built in functions quicker than looping through every cell evrey time.

Code

Sub Test()


Dim Rng1 As Range
Dim Rng2 As Range
Dim r1 As Range
Dim r2 As Range
Dim irow


Set Rng1 = Worksheets("Sheet1").Cells(1, 1)
irow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Set Rng1 = Rng1.Resize(irow - Rng1.Row + 1, 1)


Set Rng2 = Worksheets("Sheet2").Cells(1, 1)
irow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Set Rng2 = Rng2.Resize(irow - Rng2.Row + 1, 1)


On Error Resume Next
For Each r1 In Rng1.Cells
    If IsError(Application.WorksheetFunction.Match(r1.Value, Rng2, 0)) Then
        r1.Interior.ColorIndex = 6
    End If
Next


For Each r2 In Rng2.Cells
    If IsError(Application.WorksheetFunction.Match(r2.Value, Rng1, 0)) Then
        r1.Interior.ColorIndex = 6
    End If
Next


BorderLastCell Rng1.Cells(Rng1.Cells.Count + 1)
BorderLastCell Rng2.Cells(Rng2.Cells.Count + 1)


End Sub
Sub BorderLastCell(MyCell As Range)
    
    With MyCell
        .Interior.ColorIndex = 3
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
    End With
End Sub

Display More

Joozh · May 18th 2007

Re: Weird Behavior Of Debug Statements

Thanks Andy and fillipo

Joozh · May 21st 2007

Re: Weird Behavior Of Debug Statements

Hi Andy,

I went through your code in detail and learnt quite a lot of new things. Thanks

There is one problem however. I am trying to identify and find a fix but given my current VBA skills this will probably take a long time. So I will grateful if you can help. The problem is that your code higlights the cells in Sheet1 that are not in Sheet2. What confuses me is that though the code to do so is there (please see the code below)H but it does not do the same for Sheet2 i.e. higlight the cells in Sheet2 that are NOT in Sheet1)

Code

For Each r2 In Rng2.Cells 
        If IsError(Application.WorksheetFunction.Match(r2.Value, Rng1, 0)) Then 
            r1.Interior.ColorIndex = 6 
        End If 
Next

Another observation (i may be making some stupid mistake some where) is that in each iteration, the debug statement in the following code are printing the same results??? :confused:

Code

On Error Resume Next
For Each r1 In Rng1.Cells
'Debug.Print "R1.Value = " & Rng1.Cells(Rng1.Rows.Count, 1).Value
'Debug.Print "R1.Address = " & Rng1.Cells(Rng1.Rows.Count, 1).Address
    If IsError(Application.WorksheetFunction.Match(r1.Value, Rng2, 0)) Then
        r1.Interior.ColorIndex = 6
    End If
Next

Last question: Should I also put a "On Error Resume Next line" at the start of the 2nd for loop (i.e. the 1st piece of code that I have pasted above).

Advance apologies for the botheration.

Thanks.

**Andy Pope** · May 21st 2007

Re: Weird Behavior Of Debug Statements

Looks like a typo by me.

On the second test change the reference from r1 to r2

Code

For Each r2 In Rng2.Cells 
        If IsError(Application.WorksheetFunction.Match(r2.Value, Rng1, 0)) Then 
            r2.Interior.ColorIndex = 6 
        End If 
    Next

The on error is not required twice.

Joozh · May 21st 2007

Re: Weird Behavior Of Debug Statements

Andy thanks again for the quick reply.
No, your original quote was correct and no need to change the reference from r1 to r2. Unless I'm making a mistake, there seems to be a problem in the following piece of code

Code

For Each r1 In Rng1.Cells 
        If IsError(Application.WorksheetFunction.Match(r1.Value, Rng2, 0)) Then 
            r1.Interior.ColorIndex = 6 
        End If

What the code is supposed to do is to highlight (with yellow fill) each cell in each sheet which does NOT exists in the other sheet. Your code above sems to be be doing the opposite??

I'm confused

**Andy Pope** · May 21st 2007

Re: Debug.Print Causes Crash

try the attached example.

If not what you are expecting post back your data set.

Debug.Print Causes Crash

Participate now!

Share