How do debug a loop?

I'm running code that includes several loops and am getting an error that says:

"Run-time error '13':

Type mismatch"

When I get back to the code it doesn't highlight anything or give any indication of where the error is.

The loop puts calculated values into excel, so I can tell around where in the loop the error occurs from that. However, I can't easily determine the line of code that's causing the problem.

Is there a way to quickly jump to the error?

Sub GetRentIncreasesWest()

Dim ArrayCount As Integer
Dim Rchanges(1 To 176) As String
Dim CgMonth(1 To 176) As String
Dim CgYr(1 To 176) As String
Dim CgAmount(1 To 176) As String
Dim PrVar As Integer
Dim MonthNum(0 To 176) As String
Dim ProRata(1 To 176) As Variant
Dim Pro As Double
Dim CalcYr As Double
Dim Yr13 As Double
Dim Yr14 As Double
Dim Yr15 As Double
Dim Yr16 As Double
Dim Yr17 As Double
Dim Yr18 As Double
Dim Yr19 As Double
Dim count As Double
Dim YrPlace As Integer
Dim YrPlace2 As Double
Dim YrPlace3 As Double
Dim SmYrCount13 As Byte
Dim SmYrCount14 As Byte
Dim SmYrCount15 As Byte
Dim SmYrCount16 As Byte
Dim SmYrCount17 As Byte
Dim SmYrCount18 As Byte
Dim SmYrCount19 As Byte
Dim RmMth As Integer
Dim Mult As Integer
YrPlace2 = 0
Dim LMth As Integer
Dim TMth As Integer
Dim YrSwitch(2) As String
Dim Loc As String
Dim Sum As Double
Dim YrCounter As Double
Dim RowMov As Double
Mult = 4
Sum = 0
Loc = 0

YrPlace2 = 0
For ArrayCount = 1 To 176

   CgMonth(ArrayCount) = Left(Range("D176").Offset(ArrayCount, 0), 3)
   CgYr(ArrayCount) = Right(Range("D176").Offset(ArrayCount, 0), 4)
       CgAmount(ArrayCount) = Range("E176").Offset(ArrayCount, 0)
    If Range("E176").Offset(ArrayCount, 0) = "-" Or Range("E176").Offset(ArrayCount, 0) = "" Then CgAmount(ArrayCount) = 0
    
 
Next

For ArrayCount = 1 To 145


    If CgMonth(ArrayCount) = "Jan" Then MonthNum(ArrayCount) = "0"
    If CgMonth(ArrayCount) = "Feb" Then MonthNum(ArrayCount) = "1"
    If CgMonth(ArrayCount) = "Mar" Then MonthNum(ArrayCount) = "2"
    If CgMonth(ArrayCount) = "Apr" Then MonthNum(ArrayCount) = "3"
    If CgMonth(ArrayCount) = "May" Then MonthNum(ArrayCount) = "4"
    If CgMonth(ArrayCount) = "Jun" Then MonthNum(ArrayCount) = "5"
    If CgMonth(ArrayCount) = "Jul" Then MonthNum(ArrayCount) = "6"
    If CgMonth(ArrayCount) = "Aug" Then MonthNum(ArrayCount) = "7"
    If CgMonth(ArrayCount) = "Sep" Then MonthNum(ArrayCount) = "8"
    If CgMonth(ArrayCount) = "Oct" Then MonthNum(ArrayCount) = "9"
    If CgMonth(ArrayCount) = "Nov" Then MonthNum(ArrayCount) = "10"
    If CgMonth(ArrayCount) = "Dec" Then MonthNum(ArrayCount) = "11"
    If CgMonth(ArrayCount) = "-" Then MonthNum(ArrayCount) = "13"
    If CgMonth(ArrayCount) = "" Then MonthNum(ArrayCount) = "13"
    If CgMonth(ArrayCount) = "0" Then MonthNum(ArrayCount) = "13"
Next

count = 0
CalcYr13 = 0
CalcRr14 = 0
CalcYr15 = 0
CalcRr16 = 0
CalcRr17 = 0
CalcYr18 = 0
CalcRr19 = 0
Yr13 = 0
Yr14 = 0
YrCounter = 0
RowMov = 0
MonthNum(0) = "13"
SmYrCount = 0
For ArrayCount = 1 To 176
   
'MsgBox MonthNum(ArrayCount) - MonthNum(0)

    ProRata(ArrayCount) = MonthNum(ArrayCount)
  '  MsgBox ProRata(ArrayCount)
   ' MsgBox ProRata(ArrayCount)
   RmMth = 12 - (ProRata(ArrayCount))
    Pro = (RmMth / 12) * CgAmount(ArrayCount)
  '   MsgBox "Arraycount is " & " " & ArrayCount & "Year is " & CgYr(ArrayCount)
 'MsgBox "$ " & Pro
YrPlace2 = YrPlace2 + 1
YrSwitch(0) = "false"
YrSwitch(1) = "true"


If YrCounter = 4 Then
    YrCounter = 0
    Loc = Loc + 1
End If
If YrPlace2 - Mult = 0 Then

Sum = 0
    'MsgBox YrPlace2 & " " & Loc

    LMth = TMth
    TMth = TMth + 1
    Mult = Mult + 4
Else
YrSwitch(0) = YrSwitch(1)
Sum = Sum + Pro
LMth = TMth

End If

If YrPlace2 - Mult = -3 Then
    If YrSwitch(0) = "false" Then
        YrSwitch(1) = "true"
    ElseIf YrSwitch(0) = "true" Then
        YrSwitch(1) = "false"
    End If
End If
'MsgBox YrCounter
  YrPlace = Application.WorksheetFunction.RoundDown(ArrayCount / 4, 0)
 'YrPlace2 = Application.WorksheetFunction.RoundDown((ArrayCount - 1) / 4, 2)
' MsgBox YrPlace2
 ' MsgBox YrPlace2
      Select Case CgYr(ArrayCount)
    Case ""
        YrCounter = YrCounter + 1
    Case "-"
        YrCounter = YrCounter + 1
    Case 2013
        YrCounter = YrCounter + 1
        Yr13 = ArrayCount - 1
    '    Loc = Range("R9").Offset((ArrayCount / 4), 0).Address
     '   MsgBox Loc
     '   YrPlace3 = (ArrayCount - 1) Mod 4
     '   MsgBox YrPlace3
   '  MsgBox YrPlace3

        If MonthNum(ArrayCount) - MonthNum(ArrayCount - 1) > 0 And (YrSwitch(0) = YrSwitch(1)) Then

            Range("R51").Offset((Loc), 0) = Sum
        Else
            Range("R51").Offset((Loc), 0).Value = Pro
            CalcYr13 = Pro

        End If
        
    Case 2014
    YrCounter = YrCounter + 1
        Yr14 = ArrayCount - 1

        If MonthNum(ArrayCount) - MonthNum(ArrayCount - 1) > 0 And (YrSwitch(0) = YrSwitch(1)) Then

            Range("S51").Offset((Loc), 0) = Sum
        Else
            Range("S51").Offset((Loc), 0).Value = Pro
            CalcYr14 = Pro

        End If
    Case 2015
    YrCounter = YrCounter + 1
        Yr15 = ArrayCount - 1

        If MonthNum(ArrayCount) - MonthNum(ArrayCount - 1) > 0 And (YrSwitch(0) = YrSwitch(1)) Then

            Range("T51").Offset((Loc), 0) = Sum
        Else
            Range("T51").Offset((Loc), 0).Value = Pro
            CalcYr15 = Pro
        End If
        
    Case 2016
    YrCounter = YrCounter + 1
        Yr16 = ArrayCount - 1

        If MonthNum(ArrayCount) - MonthNum(ArrayCount - 1) > 0 And (YrSwitch(0) = YrSwitch(1)) Then

            Range("U51").Offset((Loc), 0) = Sum
        Else
            Range("U51").Offset((Loc), 0).Value = Pro
            CalcYr16 = Pro

        End If

     Case 2017
     YrCounter = YrCounter + 1
        Yr17 = ArrayCount - 1

        If MonthNum(ArrayCount) - MonthNum(ArrayCount - 1) > 0 And (YrSwitch(0) = YrSwitch(1)) Then

            Range("V51").Offset((Loc), 0) = Sum
        Else
            Range("V51").Offset((Loc), 0).Value = Pro
            CalcYr17 = Pro

        End If

     Case 2018
    YrCounter = YrCounter + 1
        Yr18 = ArrayCount - 1

        If MonthNum(ArrayCount) - MonthNum(ArrayCount - 1) > 0 And (YrSwitch(0) = YrSwitch(1)) Then

            Range("W51").Offset((Loc), 0) = Sum
        Else
            Range("W51").Offset((Loc), 0).Value = Pro
            CalcYr18 = Pro

        End If

      Case 2019
      YrCounter = YrCounter + 1
        Yr19 = ArrayCount - 1

        If MonthNum(ArrayCount) - MonthNum(ArrayCount - 1) > 0 And (YrSwitch(0) = YrSwitch(1)) Then

            Range("X51").Offset((Loc), 0) = Sum
        Else
            Range("X51").Offset((Loc), 0).Value = Pro
            CalcYr19 = Pro

        End If
 
  End Select
  
  


Next


End Sub

It didn't highlight on mine, but I changed it to 176 and it runs now. The logic is wrong somewhere since it outputs some values to the wrong location, but at least it runs all the way through. I'll try to fix it myself before reposting.

Originally Posted by tehneXus

Hi,

for me the following line is highlighted:

RmMth = 12 - (ProRata(ArrayCount))

caused when ArrayCount = 146 as ProRata(146) is empty because the first loop ends at 145..

Loop Calc Excel Forum Example updated.xlsx

Here is an example. Line 8 in the sample is where line 176 is in the actual one.

I'm no expert, but when I run into this situation:

1. I put a 'debug.print' statement in the loop to find out how many loops completed. The immediate window (CTRL G)
in the debugger will display the output.

For example:

For ArrayCount = 1 To 176

  debug.print "First Loop Arraycount = " & ArrayCount
  '...
Next

2. Say you find that it stops after '45' is printed.

Modify the code to start the debugger on arraycount 45. I either of two techniques:

For ArrayCount = 1 To 176

  debug.print "First Loop Arraycount = " & ArrayCount
  if ArrayCount = 45 then
    debug.assert false     'STARTS the debugger
  endif

'or

  if ArrayCount = 45 then
     ArrayCount  = ArrayCount      'Put a 'BREAKPOINT' on this line
  endif

  '...
Next