PDA

View Full Version : Someone can help me out with writing a simple function??



crazyjji
02-05-2008, 08:18 AM
Hi folks~

I just started taking a vba class so I have a minimal knowledge on function writing.

Can someone help me to write function that has the following macro functions?

=HLOOKUP(lookup_value, table_array, MATCH(lookup_value, lookup_array, [match type]), [range_lookup])


I use this a lot but it is too messy if I have to put these same function more than two in just one cell.

Thanks!!

Bob Phillips
02-05-2008, 09:28 AM
For all matches



Function MyLookup(lookup_value, hlookup_array, match_array)
Dim match_index As Long
Dim hlookup_index As Long
Dim cell As Range

Set cell = match_array.Find(lookup_value)
match_index = cell.Row - match_array.Cells(1, 1).Row + 1
Set cell = hlookup_array.Rows(1).Find(lookup_value)
hlookup_index = cell.Column - hlookup_array.Cells(1, 1).Column + 1
MyLookup = hlookup_array.Cells(match_index, hlookup_index).Value

End Function

crazyjji
02-05-2008, 12:44 PM
lookup value for hlookup and lookup value for match are different~~~





For all matches



Function MyLookup(lookup_value, hlookup_array, match_array)
Dim match_index As Long
Dim hlookup_index As Long
Dim cell As Range

Set cell = match_array.Find(lookup_value)
match_index = cell.Row - match_array.Cells(1, 1).Row + 1
Set cell = hlookup_array.Rows(1).Find(lookup_value)
hlookup_index = cell.Column - hlookup_array.Cells(1, 1).Column + 1
MyLookup = hlookup_array.Cells(match_index, hlookup_index).Value

End Function

Bob Phillips
02-05-2008, 02:46 PM
No problem



Function MyLookup(hlookup_value, hlookup_array, match_value, match_array)
Dim match_index As Long
Dim hlookup_index As Long
Dim cell As Range

Set cell = match_array.Find(match_value)
match_index = cell.Row - match_array.Cells(1, 1).Row + 1
Set cell = hlookup_array.Rows(1).Find(hlookup_value)
hlookup_index = cell.Column - hlookup_array.Cells(1, 1).Column + 1
MyLookup = hlookup_array.Cells(match_index, hlookup_index).Value

End Function