VBA Sub is not filling the excel cell with a value-CodePudding

I have the following code that is not not returning a value to the referenced cell. Any ideas?

    Sub If_And_ElseIf()
    
    'Retrieves incentive baseline for corp mgr 1
    
        Dim TeamHire As Integer
        Dim InstalltoGoal As Integer
        Dim Baseline As Integer
        
        TeamHire = Range("B3").Value
        InstalltoGoal = Range("B7").Value
        Baseline = Range("C12").Value
        
    'Line 1 of Pay Matrix
        If TeamHire < Range("I5").Value And InstalltoGoal < Range("K2").Value Then
            Baseline = 0
        ElseIf TeamHire < Range("I5").Value And InstalltoGoal >= Range("K2").Value < Range("L2").Value Then
            Baseline = 400
        ElseIf TeamHire < Range("I5").Value And InstalltoGoal >= Range("L2").Value < Range("M2").Value Then
            Baseline = 600
        ElseIf TeamHire < Range("I5").Value And InstalltoGoal >= Range("M2").Value < Range("N2").Value Then
            Baseline = 800
        ElseIf TeamHire < Range("I5").Value And InstalltoGoal >= Range("N2").Value < Range("O2").Value Then
            Baseline = 920
        ElseIf TeamHire < Range("I5").Value And InstalltoGoal >= Range("O2").Value Then
            Baseline = 1040
    'Line 2 of Pay Matrix
        ElseIf TeamHire >= Range("I5").Value < Range("I6") And InstalltoGoal < Range("K2") Then
            Baseline = 400
        ElseIf TeamHire >= Range("I5").Value < Range("I6") And InstalltoGoal >= Range("K2") < Range("L2").Value Then
            Baseline = 800
        ElseIf TeamHire >= Range("I5").Value < Range("I6") And InstalltoGoal >= Range("L2") < Range("M2").Value Then
            Baseline = 1000
        ElseIf TeamHire >= Range("I5").Value < Range("I6") And InstalltoGoal >= Range("M2") < Range("N2").Value Then
            Baseline = 1200
        ElseIf TeamHire >= Range("I5").Value < Range("I6") And InstalltoGoal >= Range("N2") < Range("O2").Value Then
            Baseline = 1320
        ElseIf TeamHire >= Range("I5").Value < Range("I6") And InstalltoGoal >= Range("O2").Value Then
            Baseline = 1440
    'Line 3 of Pay Matrix
        ElseIf TeamHire >= Range("I6").Value < Range("I7") And InstalltoGoal < Range("K2") Then
            Baseline = 600
        ElseIf TeamHire >= Range("I6").Value < Range("I7") And InstalltoGoal >= Range("K2") < Range("L2").Value Then
            Baseline = 1000
        ElseIf TeamHire >= Range("I6").Value < Range("I7") And InstalltoGoal >= Range("L2") < Range("M2").Value Then
            Baseline = 1200
        ElseIf TeamHire >= Range("I6").Value < Range("I7") And InstalltoGoal >= Range("M2") < Range("N2").Value Then
            Baseline = 1400
        ElseIf TeamHire >= Range("I6").Value < Range("I7") And InstalltoGoal >= Range("N2") < Range("O2").Value Then
            Baseline = 1520
        ElseIf TeamHire >= Range("I6").Value < Range("I7") And InstalltoGoal >= Range("O2").Value Then
            Baseline = 1640
    'Line 4 of Pay Matrix
        ElseIf TeamHire >= Range("I7").Value < Range("I8") And InstalltoGoal < Range("K2") Then
            Baseline = 800
        ElseIf TeamHire >= Range("I7").Value < Range("I8") And InstalltoGoal >= Range("K2") < Range("L2").Value Then
            Baseline = 1200
        ElseIf TeamHire >= Range("I7").Value < Range("I8") And InstalltoGoal >= Range("L2") < Range("M2").Value Then
            Baseline = 1400
        ElseIf TeamHire >= Range("I7").Value < Range("I8") And InstalltoGoal >= Range("M2") < Range("N2").Value Then
            Baseline = 1600
        ElseIf TeamHire >= Range("I7").Value < Range("I8") And InstalltoGoal >= Range("N2") < Range("O2").Value Then
            Baseline = 1720
        ElseIf TeamHire >= Range("I7").Value < Range("I8") And InstalltoGoal >= Range("O2").Value Then
            Baseline = 1840
    'Line 5 of Pay Matrix
        ElseIf TeamHire >= Range("I8").Value < Range("I9") And InstalltoGoal < Range("K2") Then
            Baseline = 920
        ElseIf TeamHire >= Range("I8").Value < Range("I9") And InstalltoGoal >= Range("K2") < Range("L2").Value Then
            Baseline = 1320
        ElseIf TeamHire >= Range("I8").Value < Range("I9") And InstalltoGoal >= Range("L2") < Range("M2").Value Then
            Baseline = 1520
        ElseIf TeamHire >= Range("I8").Value < Range("I9") And InstalltoGoal >= Range("M2") < Range("N2").Value Then
            Baseline = 1720
        ElseIf TeamHire >= Range("I8").Value < Range("I9") And InstalltoGoal >= Range("N2") < Range("O2").Value Then
            Baseline = 1840
        ElseIf TeamHire >= Range("I8").Value < Range("I9") And InstalltoGoal >= Range("O2").Value Then
            Baseline = 1960
    'Line 6 of Pay Matrix
        ElseIf TeamHire >= Range("I9").Value < Range("I10") And InstalltoGoal < Range("K2") Then
            Baseline = 1040
        ElseIf TeamHire >= Range("I9").Value < Range("I10") And InstalltoGoal >= Range("K2") < Range("L2").Value Then
            Baseline = 1440
        ElseIf TeamHire >= Range("I9").Value < Range("I10") And InstalltoGoal >= Range("L2") < Range("M2").Value Then
            Baseline = 1640
        ElseIf TeamHire >= Range("I9").Value < Range("I10") And InstalltoGoal >= Range("M2") < Range("N2").Value Then
            Baseline = 1840
        ElseIf TeamHire >= Range("I9").Value < Range("I10") And InstalltoGoal >= Range("N2") < Range("O2").Value Then
            Baseline = 1960
        ElseIf TeamHire >= Range("I9").Value < Range("I10") And InstalltoGoal >= Range("O2").Value Then
            Baseline = 2080
    'End If/Then/Else Statement
        Else: MsgBox "Error"
        End If
        
    End Sub

CodePudding user response：

You could simplify the logic using arrays

Sub If_And_ElseIf1()
    
    Dim TeamHire As Integer
    Dim InstalltoGoal As Integer
    Dim baseline, i As Integer
    
    'Retrieves incentive baseline for corp mgr 1
    'Line 1 of Pay Matrix
    TeamHire = Range("B3").Value
    If TeamHire < Range("I5").Value Then
        baseline = Array(0, 400, 600, 800, 920, 1040)
        
    'Line 2 of Pay Matrix
    ElseIf TeamHire >= Range("I5").Value And TeamHire < Range("I6").Value Then
        baseline = Array(400, 800, 1000, 1200, 1320, 1440)
        
    'Line 3 of Pay Matrix
    ElseIf TeamHire >= Range("I6").Value And TeamHire < Range("I7").Value Then
        baseline = Array(600, 1000, 1200, 1400, 1520, 1640)
        
    'Line 4 of Pay Matrix
    ElseIf TeamHire >= Range("I7").Value And TeamHire < Range("I8").Value Then
        baseline = Array(800, 1200, 1400, 1600, 1720, 1840)
        
    'Line 5 of Pay Matrix
    ElseIf TeamHire >= Range("I8").Value And TeamHire < Range("I9").Value Then
        baseline = Array(920, 1320, 1520, 1720, 1840, 1960)
        
    'Line 6 of Pay Matrix
    ElseIf TeamHire >= Range("I9").Value And TeamHire < Range("I10").Value Then
        baseline = Array(1040, 1440, 1640, 1840, 1960, 2080)
        
    'End If/Then/Else Statement
    Else
        MsgBox "Error"
        Exit Sub
    End If
    
    InstalltoGoal = Range("B7").Value
    If InstalltoGoal < Range("K2") Then
        i = 0
    ElseIf InstalltoGoal < Range("L2").Value Then
        i = 1
    ElseIf InstalltoGoal < Range("M2").Value Then
        i = 2
    ElseIf InstalltoGoal < Range("N2").Value Then
        i = 3
    ElseIf InstalltoGoal < Range("O2").Value Then
        i = 4
    Else
        i = 5
    End If
    
    ' assign to cell
    Range("C12").Value = baseline(i)
    'MsgBox baseline(i)
    
End Sub