Looking up values
 

The following two function work for looking up a value but if I make a
change to the Row/Column that contains the lookup value my lookup value does
not change. I have my sheet on automatic calculate. Should these function
types be ranges?


Function VLook(lookupValue As Range, LookupRange As Range, Column As
Integer) As Double
Dim cell As Range
For Each cell In LookupRange
If cell = lookupValue Then
VLook = cell.Offset(0, Column - 1)
End If
Next cell
End Function

Function HLook(lookupValue As Range, LookupRange As Range, Row As Integer)
As Double
Dim cell As Range
For Each cell In LookupRange
If cell = lookupValue Then
HLook = cell.Offset(Row - 1, 0)
Exit Function
End If
Next cell
End Function

Looking up values
 

I find vlookup and hlookup to finicky.


"Don Guillett" wrote in message
...
Maybe you need to add application.volatile but

Why are you re-inventing the wheel?

"Nathan Carroll" wrote in message
...
The following two function work for looking up a value but if I make a
change to the Row/Column that contains the lookup value my lookup value
does
not change. I have my sheet on automatic calculate. Should these
function
types be ranges?


Function VLook(lookupValue As Range, LookupRange As Range, Column As
Integer) As Double
Dim cell As Range
For Each cell In LookupRange
If cell = lookupValue Then
VLook = cell.Offset(0, Column - 1)
End If
Next cell
End Function

Function HLook(lookupValue As Range, LookupRange As Range, Row As
Integer)
As Double
Dim cell As Range
For Each cell In LookupRange
If cell = lookupValue Then
HLook = cell.Offset(Row - 1, 0)
Exit Function
End If
Next cell
End Function

Looking up values
 

Thanks that worked.


"Don Guillett" wrote in message
...
Maybe you need to add application.volatile but

Why are you re-inventing the wheel?

"Nathan Carroll" wrote in message
...
The following two function work for looking up a value but if I make a
change to the Row/Column that contains the lookup value my lookup value
does
not change. I have my sheet on automatic calculate. Should these
function
types be ranges?


Function VLook(lookupValue As Range, LookupRange As Range, Column As
Integer) As Double
Dim cell As Range
For Each cell In LookupRange
If cell = lookupValue Then
VLook = cell.Offset(0, Column - 1)
End If
Next cell
End Function

Function HLook(lookupValue As Range, LookupRange As Range, Row As
Integer)
As Double
Dim cell As Range
For Each cell In LookupRange
If cell = lookupValue Then
HLook = cell.Offset(Row - 1, 0)
Exit Function
End If
Next cell
End Function

Looking up values
 

OoooooooooooooooK

"Nathan Carroll" wrote in message
...
I find vlookup and hlookup to finicky.


"Don Guillett" wrote in message
...
Maybe you need to add application.volatile but

Why are you re-inventing the wheel?

"Nathan Carroll" wrote in message
...
The following two function work for looking up a value but if I make a
change to the Row/Column that contains the lookup value my lookup
value
does
not change. I have my sheet on automatic calculate. Should these
function
types be ranges?


Function VLook(lookupValue As Range, LookupRange As Range, Column As
Integer) As Double
Dim cell As Range
For Each cell In LookupRange
If cell = lookupValue Then
VLook = cell.Offset(0, Column - 1)
End If
Next cell
End Function

Function HLook(lookupValue As Range, LookupRange As Range, Row As
Integer)
As Double
Dim cell As Range
For Each cell In LookupRange
If cell = lookupValue Then
HLook = cell.Offset(Row - 1, 0)
Exit Function
End If
Next cell
End Function