lundi 30 novembre 2015

VBA Run-time Error 1004 Nested If statement

The purpose of my code is to determine if a cell falls within a 'feasible region'. If it does, apply a formula based on the location of the cell, and if it does not, put a "|" into the cell.

I have run this same code at a smaller scale and had no problems. I have stepped into the code and it allows the For loop to run hundreds of times before returning a "Run-time error '1004': Application-defined or object defined error"

Sub DPCalc()

Dim Pur As Integer
Dim ws As Worksheet
Dim LookUpRange As Range
Dim State As Integer

Set ws = ThisWorkbook.Sheets("DPExpansion")

 For C = 5 To 74
    For r = 6 To 278
    Pur = Cells(1, C).Value
        If Pur <= Cells(r, 4).Value And Pur >= Cells(r, 3).Value Then
' Year 2055
            If r <= 10 Then
                Cells(r, C).Value = (10 * Pur ^ 0.75) * (1.05 ^ -40)

' Year 2050
            ElseIf r <= 19 And r > 10 Then
                Set LookUpRange = Range("B6 : BW10")
                State = Cells(r, 2).Value + Cells(1, C).Value
                Cells(r, C).Value = (10 * Pur ^ 0.75) * (1.05 ^ -35) + Application.WorksheetFunction.VLookup(Value, LookUpRange, 74, False)

' Year 2045
            ElseIf r <= 38 And r > 19 Then
                Set LookUpRange = Range("B11 : BW19")
                State = Cells(r, 2).Value + Cells(1, C).Value
                Cells(r, C).Value = (10 * Pur ^ 0.75) * (1.05 ^ -30) + Application.WorksheetFunction.VLookup(Value, LookUpRange, 74, False)

' Year 2040
            ElseIf r <= 63 And r > 38 Then
                Set LookUpRange = Range("B20 : BW38")
                State = Cells(r, 2).Value + Cells(1, C).Value
                Cells(r, C).Value = (10 * Pur ^ 0.75) * (1.05 ^ -25) + Application.WorksheetFunction.VLookup(Value, LookUpRange, 74, False)

' Year 2035
            ElseIf r <= 98 And r > 63 Then
                Set LookUpRange = Range("B39 : BW63")
                State = Cells(r, 2).Value + Cells(1, C).Value
                Cells(r, C).Value = (10 * Pur ^ 0.75) * (1.05 ^ -20) + Application.WorksheetFunction.VLookup(Value, LookUpRange, 74, False)

' Year 2030
            ElseIf r <= 148 And r > 98 Then
                Set LookUpRange = Range("B64 : BW98")
                State = Cells(r, 2).Value + Cells(1, C).Value
                Cells(r, C).Value = (10 * Pur ^ 0.75) * (1.05 ^ -15) + Application.WorksheetFunction.VLookup(Value, LookUpRange, 74, False)

' Year 2025
            ElseIf r <= 208 And r > 148 Then
                Set LookUpRange = Range("B99 : BW148")
                State = Cells(r, 2).Value + Cells(1, C).Value
               Cells(r, C).Value = (10 * Pur ^ 0.75) * (1.05 ^ -10) + Application.WorksheetFunction.VLookup(Value, LookUpRange, 74, False)

' Year 2020
            Else
                Set LookUpRange = Range("B149 : BW208")
                State = Cells(r, 2).Value + Cells(1, C).Value
                Cells(r, C).Value = (10 * Pur ^ 0.75) * (1.05 ^ -5) + Application.WorksheetFunction.VLookup(Value, LookUpRange, 74, False)
               End If
        Else
        Cells(r, C).Value = "|"
        End If
     Next r
    Next C

End Sub

I have also tried commenting out the portion that adds the value based on a vlookup, and that had no impact.

Aucun commentaire:

Enregistrer un commentaire