Run-Time Error 1004

Hello all,

Please see my code below. It was working fine for awhile, but is now bugging out. Originally, it only bugged out at the highlighted and underlined code in case 13 whenever the first letter of the column numbers switched (e.g. AZ to BA), but now it is bugging out in every column. The error I'm getting is run-time error 1004. Any idea how to fix this?

Additionally, now the sub is inserting a "\" into the countif function, so you get: Countif(G\15:g\4,"<"&0). Any idea what it's adding the "\"s? Any ideas how to fix?

I deleted Cases 2 through 8 so that it would fit.

Thanks!
Mattman123

Sub doalltheshit()

Dim accountnumber As Integer

For accountnumber = 210 To 210
    producedrow = 4
    
    For test = 3 To 28002
        
        lookback = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 11)
        stdevcoeff = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 12)
        minvar = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 9)
        tolerance = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 13)
        recurrence = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 10)
        For Scenario = 1 To 13
            Select Case Scenario
                Case 1
                    'Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 1) = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 8)
                    'Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 2) = "D"
                    'Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 3) = "11/1/2013"
                    
                    monthsamount = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 22)
                    If recurrence = "Monthly" Then
                        runningtotal = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 20)
                        For lkbck = 1 To lookback
                            If lkbck <> 1 Then
                                runningtotal = runningtotal + Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 20 - 2 * (lkbck - 1))
                            End If
                        Next lkbck
                        thataverage = runningtotal / lookback
                        SumSq = 0
                        For lkbck = 1 To lookback
                            SumSq = SumSq + (Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 20 - 2 * (lkbck - 1)) - thataverage) ^ 2
                        Next lkbck
                    Else
                        runningtotal = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 18)
                        For lkbck = 1 To lookback
                            If lkbck <> 1 Then
                                runningtotal = runningtotal + Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 18 - 2 * (lkbck - 1))
                            End If
                        Next lkbck
                        thataverage = runningtotal / lookback
                        SumSq = 0
                        For lkbck = 1 To lookback
                            SumSq = SumSq + (Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 18 - 2 * (lkbck - 1)) - thataverage) ^ 2
                        Next lkbck
                    End If
                    
                    
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, accountnumber) = (1 + tolerance) * (thataverage + stdevcoeff * Sqr(SumSq / (lookback - 1))) + minvar - monthsamount
                Case 9
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 1) = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 8)
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 2) = "D"
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 3) = "3/1/2014"
                     
                    monthsamount = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 30)
                    If recurrence = "Monthly" Then
                        runningtotal = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 28)
                        For lkbck = 1 To lookback
                            If lkbck <> 1 Then
                                runningtotal = runningtotal + Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 28 - 2 * (lkbck - 1))
                            End If
                        Next lkbck
                        thataverage = runningtotal / lookback
                        SumSq = 0
                        For lkbck = 1 To lookback
                            SumSq = SumSq + (Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 28 - 2 * (lkbck - 1)) - thataverage) ^ 2
                        Next lkbck
                    Else
                        runningtotal = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 24)
                        For lkbck = 1 To lookback
                            If lkbck <> 1 Then
                                runningtotal = runningtotal + Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 24 - 2 * (lkbck - 1))
                            End If
                        Next lkbck
                        thataverage = runningtotal / lookback
                        SumSq = 0
                        For lkbck = 1 To lookback
                            SumSq = SumSq + (Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 24 - 2 * (lkbck - 1)) - thataverage) ^ 2
                        Next lkbck
                    End If
                    
              
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, accountnumber) = (1 + tolerance) * (thataverage + stdevcoeff * Sqr(SumSq / (lookback - 1))) + minvar - monthsamount
                Case 10
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 1) = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 8)
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 2) = "W"
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 3) = "3/1/2014"
                    
                    monthsamount = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 31)
                    If recurrence = "Monthly" Then
                        runningtotal = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 29)
                        For lkbck = 1 To lookback
                            If lkbck <> 1 Then
                                runningtotal = runningtotal + Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 29 - 2 * (lkbck - 1))
                            End If
                        Next lkbck
                        thataverage = runningtotal / lookback
                        SumSq = 0
                        For lkbck = 1 To lookback
                            SumSq = SumSq + (Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 29 - 2 * (lkbck - 1)) - thataverage) ^ 2
                        Next lkbck
                    Else
                        runningtotal = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 25)
                        For lkbck = 1 To lookback
                            If lkbck <> 1 Then
                                runningtotal = runningtotal + Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 25 - 2 * (lkbck - 1))
                            End If
                        Next lkbck
                        thataverage = runningtotal / lookback
                        SumSq = 0
                        For lkbck = 1 To lookback
                            SumSq = SumSq + (Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 25 - 2 * (lkbck - 1)) - thataverage) ^ 2
                        Next lkbck
                    End If
                    
                    
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, accountnumber) = (1 + tolerance) * (thataverage + stdevcoeff * Sqr(SumSq / (lookback - 1))) + minvar - monthsamount
                Case 11
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 1) = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 8)
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 2) = "D"
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 3) = "4/1/2014"
                    
                    onthsamount = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 32)
                    If recurrence = "Monthly" Then
                        runningtotal = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 30)
                        For lkbck = 1 To lookback
                            If lkbck <> 1 Then
                                runningtotal = runningtotal + Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 30 - 2 * (lkbck - 1))
                            End If
                        Next lkbck
                        thataverage = runningtotal / lookback
                        SumSq = 0
                        For lkbck = 1 To lookback
                            SumSq = SumSq + (Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 30 - 2 * (lkbck - 1)) - thataverage) ^ 2
                        Next lkbck
                    Else
                        runningtotal = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 30)
                        For lkbck = 1 To lookback
                            If lkbck <> 1 Then
                                runningtotal = runningtotal + Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 30 - 2 * (lkbck - 1))
                            End If
                        Next lkbck
                        thataverage = runningtotal / lookback
                        SumSq = 0
                        For lkbck = 1 To lookback
                            SumSq = SumSq + (Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 30 - 2 * (lkbck - 1)) - thataverage) ^ 2
                        Next lkbck
                    End If
                   
                    
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, accountnumber) = (1 + tolerance) * (thataverage + stdevcoeff * Sqr(SumSq / (lookback - 1))) + minvar - monthsamount
                Case 12
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 1) = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 8)
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 2) = "W"
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 3) = "4/1/2014"
                    
                    onthsamount = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 33)
                    If recurrence = "Monthly" Then
                        runningtotal = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 31)
                        For lkbck = 1 To lookback
                            If lkbck <> 1 Then
                                runningtotal = runningtotal + Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 31 - 2 * (lkbck - 1))
                            End If
                        Next lkbck
                        thataverage = runningtotal / lookback
                        SumSq = 0
                        For lkbck = 1 To lookback
                            SumSq = SumSq + (Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 31 - 2 * (lkbck - 1)) - thataverage) ^ 2
                        Next lkbck
                    Else
                        runningtotal = Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 31)
                        For lkbck = 1 To lookback
                            If lkbck <> 1 Then
                                runningtotal = runningtotal + Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 31 - 2 * (lkbck - 1))
                            End If
                        Next lkbck
                        thataverage = runningtotal / lookback
                        SumSq = 0
                        For lkbck = 1 To lookback
                            SumSq = SumSq + (Worksheets("TRANSACTIONS BY MONTH").Cells(accountnumber - 1, 31 - 2 * (lkbck - 1)) - thataverage) ^ 2
                        Next lkbck
                    End If
                   
                    
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, accountnumber) = (1 + tolerance) * (thataverage + stdevcoeff * Sqr(SumSq / (lookback - 1))) + minvar - monthsamount
                Case 13
                    Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, 1) = Worksheets("TEST MODELS AND VARIABLES").Cells(test, 8) & " # of Exceptions"
            Worksheets("PROFILE EXCEPTIONS TESTS").Cells(producedrow, accountnumber).Formula = "=COUNTIF(" & converttoletter(accountnumber) & producedrow - 1 & ":" & converttoletter(accountnumber) & producedrow - 12 & ",""<""&0)"
            End Select
            producedrow = producedrow + 1
        Next Scenario
    Next test
Next accountnumber

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Function converttoletter(icol As Integer) As String

Dim ialpha As Integer
Dim iremainder As Integer
ialpha = Int(icol / 27)
iremainder = icol - (ialpha * 26)
If ialpha > 0 Then
    converttoletter = Chr(ialpha + 64)
End If
If iremainder > 0 Then
    converttoletter = converttoletter & Chr(iremainder + 64)
End If

End Function