Excessive If Statements

[rest8223]

Hello,

I'm fairly new to VBA and I was wondering if anyone could offer some suggestions to cut down on the ammount of If statements used in my code. This is only a snippet as there are about 126 arrays with anywhere from 5 to 136 elements per array so the less I need to use If statements the better.

Dim angAcc As String, angAccFac As Double, angAccMax As Integer, angAccFacMax As Integer, iCount_angAcc As Integer, iCount_angAccFac As Integer
baseunit = Sheet1.Range("C2")
convunit = Sheet1.Range("C4")

angAccMax = Sheet2.Range("Acceleration_Angular").Count
ReDim angAcc(1 To angAccMax)
For iCount_angAcc = 1 To angAccMax
angAcc(iCount_angAcc) = Sheet2.Range("Acceleration_Angular")(iCount_angAcc)
Next iCount_angAcc

angAccFacMax = Sheet2.Range("Acceleration_Angular_Factors").Count
ReDim angAccFac(1 To angAccFacMax)
For iCount_angAccFac = 1 To angAccFacMax
angAccFac(iCount_angAccFac) = Sheet2.Range("Acceleration_Angular_Factors")(iCount_angAccFac)
Next iCount_angAccFac

If baseunit = angAcc(1) Then
basefactor = angAccFac(1)
Else
If baseunit = angAcc(2) Then
basefactor = angAccFac(2)
Else
If baseunit = angAcc(3) Then
basefactor = angAccFac(3)
Else
If baseunit = angAcc(4) Then
basefactor = angAccFac(4)
Else
If baseunit = angAcc(5) Then
basefactor = angAccFac(5)
End If
End If
End If
End If
End If
If convunit = angAcc(1) Then
convfactor = angAccFac(1)
Else
If convunit = angAcc(2) Then
convfactor = angAccFac(2)
Else
If convunit = angAcc(3) Then
convfactor = angAccFac(3)
Else
If convunit = angAcc(4) Then
convfactor = angAccFac(4)
Else
If convunit = angAcc(5) Then
convfactor = angAccFac(5)
End If
End If
End If
End If
End If

 

[AlphaFrog]

    BaseUnit = Sheet1.Range("C2")
    convunit = Sheet1.Range("C4")
    
    With Application.WorksheetFunction
    
        If .CountIf(Sheet2.Range("Acceleration_Angular"), BaseUnit) > 0 Then
            basefactor = .Index(Sheet2.Range("Acceleration_Angular_Factors"), .Match(BaseUnit, Sheet2.Range("Acceleration_Angular"), 0))
        Else
            MsgBox "No match found for Base Unit: " & BaseUnit
        End If
        
        If .CountIf(Sheet2.Range("Acceleration_Angular"), convunit) > 0 Then
            convfactor = .Index(Sheet2.Range("Acceleration_Angular_Factors"), .Match(convunit, Sheet2.Range("Acceleration_Angular"), 0))
        Else
            MsgBox "No match found for Conversion Unit: " & convunit
        End If
        
    End With

 

[rest8223]

Absolutely brilliant! Thank you, you've saved my tired little fingers a whole bunch of typing.