The following is Part 1 of a custom function problem I'm having. Thank you for your assistance.
I'm getting a compile error: Expected array and GetTableIncr is highlighted in the Cat1= formula.
In my cell I have it structured as I=3000,Period=Biweekly,Cntry=CA
Basically, if I falls between two values, it should produce a number ranging from 1 to 6.
Cheers,
-- G
Code:
Function FindFedIntPg(I As Long, Period As String, Cntry As String)
Dim GetFedTableBase As Variant
Dim Tween As Boolean
Dim GetTableIncr As Double
Dim Cat1, Cat2, Cat3, Cat4, Cat5, Cat6, Cat7, Cat8 As Boolean
Dim Pg As Double
If Pg = 1 Then
GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
Array(2, 4, 4, 8))
ElseIf Pg = 2 Then
GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
Array(4, 8, 8, 18))
ElseIf Pg = 3 Then
GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
Array(8, 16, 18, 34))
ElseIf Pg = 4 Then
GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
Array(12, 24, 26, 52))
ElseIf Pg = 5 Then
GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
Array(16, 32, 34, 70))
ElseIf Pg = 6 Then
GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
Array(20, 40, 44, 86))
End If
If Cntry = "CA" Then
GetFedTableBase = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
Array(248, 495, 537, 1072))
ElseIf Cntry = "OC" Then
GetFedTableBase = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
Array(248, 495, 537, 1072))
End If
Cat1 = GetFedTableBase(Cntry, Period) - GetFedTableBase(Cntry, Period)
Cat2 = GetFedTableBase(Cntry, Period)
Cat3 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period)
Cat3 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period) + 55 * (GetTableIncr(2, Period))
Cat4 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period) + 55 * (GetTableIncr(2, Period)) + 55 * (GetTableIncr(3, Period))
Cat5 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period) + 55 * (GetTableIncr(2, Period)) + 55 * (GetTableIncr(3, Period)) _
+ 55 * (GetTableIncr(4, Period))
Cat6 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period) + 55 * (GetTableIncr(2, Period)) + 55 * (GetTableIncr(3, Period)) _
+ 55 * (GetTableIncr(4, Period)) + 55 * (GetTableIncr(5, Period))
Cat7 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period) + 55 * (GetTableIncr(2, Period)) + 55 * (GetTableIncr(3, Period)) _
+ 55 * (GetTableIncr(4, Period)) + 55 * (GetTableIncr(5, Period)) + 55 * (GetTableIncr(6, Period)) - 0.01
If Target >= Low And Target <= High Then
Tween = True
Else
Tween = False
End If
If Tween(I, Cat1, Cat2) = True Then FindFedIntPg = 1
ElseIf Tween(I, Cat2, Cat3) = True Then FindFedIntPg = 2
ElseIf Tween(I, Cat3, Cat4) = True Then FindFedIntPg = 3
ElseIf Tween(I, Cat4, Cat5) = True Then FindFedIntPg = 4
ElseIf Tween(I, Cat5, Cat6) = True Then FindFedIntPg = 5
ElseIf Tween(I, Cat6, Cat7) = True Then FindFedIntPg = 6
End If
End Function
I'm getting a compile error: Expected array and GetTableIncr is highlighted in the Cat1= formula.
In my cell I have it structured as I=3000,Period=Biweekly,Cntry=CA
Basically, if I falls between two values, it should produce a number ranging from 1 to 6.
Cheers,
-- G