Challenge to you (If statment error & Vlookup)

[ENGinTraining]

I am attempting to write a code to look up values from a sheet in my excel file. If the input doesn't match exactly to a value in the chart then I want the program to progressively add and subtract 1 from the value until it finds a true value.

Can any first tell me why when I compile it, it gives me an error saying "End If without Block If". Also, if my lookups look correct, this is my first time writing something like this.

Code:

Function sat_state3_v(state3_p As Double, state3_v As Double) As Long


Dim find, find2, Plookup, checkloop As String, checkloop2 As String, new_p As Double, orig_p As Double, orig_p2 As Double, HP_side As Double, LP_side As Double, check As String, check2 As String, High_pressure As Double, Low_pressure As Double, high_temp As Double, low_temp As Double

Dim Table_A5E As Range
Set Table_A5E = Worksheets("A-5E (Sat Water)").Range("A9:F619")


orig_p = Application.WorksheetFunction.VLookup(state3_p, Table_A5E, 2, False)
check = IsError(orig_p)

If check = "True" Then Do
High_pressure = state3_p + 1

HP_side = Application.WorksheetFunction.VLookup(High_pressure, Table_A5E, 2, False)
checkloop = IsError(HP_side)

Loop Until checkloop = "False"
End If


orig_p2 = Application.WorksheetFunction.VLookup(state3_p, Table_A5E, 2, False)
check2 = IsError(orig_p2)

If check2 = "True" Then Do
Low_pressure = state3_p - 1

LP_side = Application.WorksheetFunction.VLookup(Low_pressure, Table_A5E, 2, False)
checkloop2 = IsError(LP_side)

Loop Until checkloop2 = "False"
End If

new_volume = (((state3_p - Low_pressure) / (High_pressure - Low_pressure)) * (HP_side - LP_side) + LP_side)
sat_state3_v = new_volume

 

[CharlesH]

Hi,


I made some correction to your code. Notice where the "Do" is placed. I also added the "End Function"

Function sat_state3_v(state3_p As Double, state3_v As Double) As Long


Dim find, find2, Plookup, checkloop As String, checkloop2 As String, new_p As Double, orig_p As Double, orig_p2 As Double, HP_side As Double, LP_side As Double, check As String, check2 As String, High_pressure As Double, Low_pressure As Double, high_temp As Double, low_temp As Double

Dim Table_A5E As Range
Set Table_A5E = Worksheets("A-5E (Sat Water)").Range("A9:F619")


orig_p = Application.WorksheetFunction.VLookup(state3_p, Table_A5E, 2, False)
check = IsError(orig_p)

If check = "True" Then
Do
High_pressure = state3_p + 1

HP_side = Application.WorksheetFunction.VLookup(High_pressure, Table_A5E, 2, False)
checkloop = IsError(HP_side)

Loop Until checkloop = "False"
End If


orig_p2 = Application.WorksheetFunction.VLookup(state3_p, Table_A5E, 2, False)
check2 = IsError(orig_p2)

If check2 = "True" Then
Do
Low_pressure = state3_p - 1

LP_side = Application.WorksheetFunction.VLookup(Low_pressure, Table_A5E, 2, False)
checkloop2 = IsError(LP_side)

Loop Until checkloop2 = "False"
End If

new_volume = (((state3_p - Low_pressure) / (High_pressure - Low_pressure)) * (HP_side - LP_side) + LP_side)
sat_state3_v = new_volume
End Function

 

[ENGinTraining]

Thank you CharlesH the code now has no errors.
But in the actual excel i get a "#Value!" error, any idea what could cause that?

"=sat_state3_v(state3_p)" <--- That is what is actually posted in the cell.

 

[CharlesH]

hi,

Sorry, I can not tell what is causing your error. Perhaps another member can.

 

[AlphaFrog]

You custom function requires two input arguments

Function sat_state3_v([COLOR="Red"]state3_p As Double[/COLOR], [COLOR="Blue"]state3_v As Double[/COLOR]) As Long

But your formula is only supplying one input argument.
=sat_state3_v(state3_p)


The code doesn't use state3_v As Double. So perhaps you could just remove it.

Function sat_state3_v([COLOR="Red"]state3_p As Double[/COLOR]) As Long

 

[shg]

Try this:

Function sat_state3_v(state3_p As Double) As Long
    Dim rA5E        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 rA5E = Worksheets("A-5E (Sat Water)").Range("A9:F619")
    On Error Resume Next
 
    orig_p = Application.VLookup(state3_p, rA5E, 2, False)
    Do While Err.Number
        Err.Clear
        dPresHi = state3_p + 1
        dSideHi = Application.VLookup(dPresHi, rA5E, 2, False)
    Loop
 
    orig_p2 = Application.WorksheetFunction.VLookup(state3_p, rA5E, 2, False)
    Do While Err.Number
        Err.Clear
        dPresLo = state3_p - 1
        dSideLo = Application.VLookup(dPresLo, rA5E, 2, False)
    Loop
 
    sat_state3_v = ((state3_p - dPresLo) / (dPresHi - dPresLo)) * (dSideHi - dSideLo) + dSideLo
End Function

You should change the function, though, to pass the range as an argument. Otherwise Excel will see no dependency.

 

[ENGinTraining]

Thank you guys. I did what alphadog suggested and it worked. Im gonna use shg's for a different part of my program. thank you guys. But I have one last question. When returning the program I get a value, but with no decimal places, and im using the Double, so shouldn't that have decimal places? or what data type should I use?

 

[ENGinTraining]

Got it, the function needed to be set as a double