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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.