Excel VBA If...Then Function not working

[VBA_noob_]

Hello,

I'm new to VBA and the board so I'm sorry if I'm unable to explain myself very well or if I've posted this in the wrong place. I have been trying to create a function with the "if then statement" to calulate the cost of a produce all with differing angles, finish, pipe size and product size. I need 3 criteria to be true to determine where the formula gets its final cost from.

For example, if a pipe is .05, the insolation is 1, the degree is 45, and there is no finish it would look in range 'Finish Cost'!$A$51:$N$92 to determine the cost from the table.

My original code was an extremely long life statement:

=IF(AND(H4=45,I4=""),VLOOKUP(E4,'Finish Cost'!$A$51:$N$92,F4*2,FALSE),
IF(AND(H4=45,I4="S"),VLOOKUP(E4,'Finish Cost'!$P$51:$AC$92,F4*2,FALSE),
IF(AND(H4=45,I4="H"),VLOOKUP(E4,'Finish Cost'!$AE$51:$AR$92,F4*2,FALSE),
IF(AND(H4=45,I4="HB"),VLOOKUP(E4,'Finish Cost'!$AT$51:$BG$92,F4*2,FALSE),
IF(AND(H4=90,I4=""),VLOOKUP(E4,'Finish Cost'!$A$4:$N$45,F4*2,FALSE),
IF(AND(H4=90,I4="S"),VLOOKUP(E4,'Finish Cost'!$P$4:$AC$45,F4*2,FALSE),
IF(AND(H4=90,I4="H"),VLOOKUP(E4,'Finish Cost'!$AE$4:$AR$45,F4*2,FALSE),VLOOKUP(E4,'Finish Cost'!$AT$4:$BG$45,F4*2,FALSE))))))))

Which worked fine until more finishes were added. So I thought I would try to make a VBA function to do it for me but it's not working for me.

Function CalcCost(pS As Byte, F1 As String, F2 As String) As Currency
On Error Resume Next
For X = 3 To Range("F" & Rows.Count).End(xlUp).Row
    If pS = "45" And F1 = "" And F2 = "" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("A51:N92"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "45" And F1 = "S" And F2 = "STRATAFAB" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("P51:AC92"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "45" And F1 = "H" And F2 = "HYDROCAL BONDED" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AE51:AR92"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "45" And F1 = "HB" And F2 = "HYDROCAL LAM & B/C" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AT51:BG92"), Sheet1.Range("X") * 2, False)
    ElseIf pSize = "45" And F1 = "H" And F2 = "HEX" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("P51:AC92"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "45" And F1 = "Q" And F2 = "QUAD" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AE51:AR92"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "" And F2 = "" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("A4:N45"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "S" And F2 = "STRATAFAB" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("P4:AC45"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "H" And F2 = "HYDROCAL BONDED" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AE4:AR45"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "HB" And F2 = "HYDROCAL LAM & B/C" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AT4:BG45"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "H" And F2 = "HEX" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("P4:AC45"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "Q" And F2 = "QUAD" Then
                Res = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AE4:AR45"), Sheet1.Range("X") * 2, False)
            Else
                Res = 0
End If
Next
End Function

Any help would be greatly appriciated.

 

[VBA_noob_]

Can anyone help me with this problem?

 

[VBA_noob_]

Fix a few silly mistakes but it's still returning 0.

Function CalcCost(pS As Byte, F1 As String, F2 As String) As Variant
For X = 3 To Range("F" & ActiveCell.Row())
    If pS = "45" & F1 = "" & F2 = "" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("A51:N92"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "45" And F1 = "S" And F2 = "STRATAFAB" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("P51:AC92"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "45" And F1 = "H" And F2 = "HYDROCAL BONDED" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AE51:AR92"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "45" And F1 = "HB" And F2 = "HYDROCAL LAM & B/C" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AT51:BG92"), Sheet1.Range("X") * 2, False)
    ElseIf pSize = "45" And F1 = "H" And F2 = "HEX" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("P51:AC92"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "45" And F1 = "Q" And F2 = "QUAD" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AE51:AR92"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "" And F2 = "" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("A4:N45"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "S" And F2 = "STRATAFAB" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("P4:AC45"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "H" And F2 = "HYDROCAL BONDED" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AE4:AR45"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "HB" And F2 = "HYDROCAL LAM & B/C" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AT4:BG45"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "H" And F2 = "HEX" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("P4:AC45"), Sheet1.Range("X") * 2, False)
    ElseIf pS = "90" And F1 = "Q" And F2 = "QUAD" Then
                CalcCost = Application.WorksheetFunction.VLookup(Sheet1.Range("pS"), Sheet11.Range("AE4:AR45"), Sheet1.Range("X") * 2, False)
            Else
                
End If
Next
End Function

Any ideas why it's still not working?