Excel VBA If...Then Function not working

VBA_noob_

New Member
Joined
Nov 14, 2011
Messages
3
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:
Code:
=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.

Code:
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Fix a few silly mistakes but it's still returning 0.

Code:
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?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top