Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
Excel 2007 doesnt show Y-axis values when the values are small. | Charts and Charting in Excel | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
Advanced Filter for Values in Column M greater than Values in Colu | Excel Discussion (Misc queries) | |||
Predict Y-values on new X-values based on other actual X and Y values? | Excel Programming |