double vlookup in excel vba

[ENGinTraining]

I attempting to search a table in excel for 2 variables. I already made a column containing both variables, formatted as "A|B".

The excel code would be =vlookup(state8s_p & "|" & state8s_s,'Table A-6E(P|s)'A6:H616!,5,False)

The above code works in a cell in excel, but I am trying to write it in Visual Basic, and in the cell it gives me a #Name? error. Any Idea why?

Code:

Function testdouble(state8s_p As Double, state8s_s As Double) As Double


Dim rA6E As Range
Dim orig_p As Double
Dim orig_p2 As Double
Dim dSideHi As Double
Dim dSideLo As Double
Dim dPresHi As Double
Dim dPresLo As Double


Set rA6E = Worksheets("Table A-6E(P|s)").Range("A6:H616")



orig_p = Application.VLookup(state8s_p + "|" + state8s_s, rA6E, 5, False)
testdouble = orig_p

End Function

There are a few extra variables, because this is only part of a larger program, but this is the part that is giving me an error.

 

[ENGinTraining]

=testdouble(state8s_p,state8s_s) <---- that is what is actually posted in the cell

 

[MrKowz]

Why use a VBA function? This can be done with native excel formulas, and without the need to create a column that contains both variables.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Joe</td><td style=";">Apple</td><td style="text-align: right;;">1</td><td style=";">Name</td><td style=";">Jane</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Jane</td><td style=";">Apple</td><td style="text-align: right;;">2</td><td style=";">Fruit</td><td style=";">Orange</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Alan</td><td style=";">Apple</td><td style="text-align: right;;">3</td><td style=";">Value</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Joe</td><td style=";">Orange</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Jane</td><td style=";">Orange</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Alan</td><td style=";">Orange</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">{=INDEX(<font color="Blue">C1:C6,MATCH(<font color="Red">1,IF(<font color="Green">A1:A6=E1,IF(<font color="Purple">B1:B6=E2,1,""</font>)</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[ENGinTraining]

Im using a vba function becasue, the total function does a vlookup and if it doesnt find an exact match it adds .0001 and subtracts .0001 until it finds two real values, and then interpolates using the two found values to give me the value I need.

 

[MrKowz]

I see - well to find the problem in the original code:

Function testdouble(state8s_p As Double, state8s_s As Double) As Double


Dim rA6E As Range
Dim orig_p As Double
Dim orig_p2 As Double
Dim dSideHi As Double
Dim dSideLo As Double
Dim dPresHi As Double
Dim dPresLo As Double


Set rA6E = Worksheets("Table A-6E(P|s)").Range("A6:H616")



orig_p = Application.VLookup(state8s_p [COLOR=red][B]&[/B][/COLOR] "|" [COLOR=red][B]&[/B][/COLOR] state8s_s, rA6E, 5, False)
testdouble = orig_p

End Function

 

[ENGinTraining]

I changed all of the +'s to & like you said and it still gives me a #Name? error in excel. The entire code is posted below, I get the same error for both the test and the actual, thats why I believe the error is in the way I am using the Vlookup but I can not figure out why.

code:
Function sup_state8_hs(state8s_p As Double, state8s_s As Double) As Double

Dim rA6E As Range
Dim orig_p As Double
Dim orig_p2 As Double
Dim dSideHi As Double
Dim dSideLo As Double
Dim dPresHi As Double
Dim dPresLo As Double

dPresHi = state8s_s
dPresLo = state8s_s


Set rA6E = Worksheets("Table A-6E(P|s)").Range("A6:H616")
count = 0
On Error Resume Next

orig_p = Application.VLookup(state8s_p & "|" & state8s_s, rA6E, 5, False)
Do While Err.Number
Err.Clear
count = count + 1
dPresHi = dPresHi + 0.0001
dSideHi = Application.VLookup(state8s_p & "|" & dPresHi, rA6E, 5, False)
Loop

orig_p2 = Application.WorksheetFunction.VLookup(state8s_p & "|" & state8s_s, rA6E, 5, False)
Do While Err.Number
Err.Clear
count = count + 1
dPresLo = dPresLo - 0.0001
dSideLo = Application.VLookup(state8s_p & "|" & dPresLo, rA6E, 5, False)
Loop
If count > 0 Then

sup_state8_hs = ((state8s_s - dPresLo) / (dPresHi - dPresLo)) * (dSideHi - dSideLo) + dSideLo
Else
sup_state8_hs = orig_p
End If

End Function

 

[shg]

If you get a #NAME error in Excel, it's because you have misspelled the function name, or the code is someplace it doesn't belong. It should be in a code module, not a sheet module or ThisWorkbook.

 

[ENGinTraining]

=sup_state8_hs(state8s_p,state8s_s) <---- this is what is typed into the excel cell, and the code is listed in the code modules, the same place the other codes were listed and they work fine. For the life of me I cant find anything misspelled. Thanks again Shg, this code is edited from what you gave me before.

 

[shg]

Are macros enabled?

 

[ENGinTraining]

yes macros are enabled. I have 3 other vba programs which all run fine.