Before you read any further, you should read @paulag 's post on the Excel Online Summit. Then take a little time to watch day 1's presentations.
https://steemit.com/steem/@paulag/promoting-steem-at-the-excel-online-summit-how-do-you-think-i-did
Especially her presentation. Once she starts to merge blockchain with Excel Learning, the host's mind is blown.
Back in around 2013, I was frustrated with vlookup function. It was very close to what I wanted, but there were a couple things that bothered me about the function. I was fluent in the Match/Index/Offset functions and their method of duplicating the capabilities of vlookup, but I wanted to share this with folks at work who weren't as comfortable.
As the folks in the Excel Online Summit pointed out, always try to use the built in functions first before you break into the VBA.
I tried to keep the "look and feel" of the function similar to vlookup, so it would be reasonably simple for folks to switch between the two.
These are the parameters that I included in the formula:
- the value you are searching for
- the range you are searching in
- the column within the range that you want to search (does not need to be the leftmost column)
- the column within the range that you want to return
- do you want to treat numbers and strings the same (so 1 equals "1")
- if no match is found, what would you like to return
- do you want to search for more than one match
- if there are more than one match, what will delimit the results (before dynamic arrays ;-) )
I apologize in advance for the lack of comments in the code.
Function vlookup2(lookup_value As Variant, table_array As Range, column_to_search As Integer, column_to_return As Integer, _
Optional treat_numbers_as_string As Boolean = False, _
Optional result_if_not_found As String = "Not Found", _
Optional search_for_multiples As Boolean = False, _
Optional delim_if_multiples As String = "||") As String
Dim rTemp As Range, bFound As Boolean, sResult As String, sSearchString As String
Dim iFoundCount As Integer
bFound = False
iFoundCount = 0
sResult = ""
If treat_numbers_as_string Then
For Each rTemp In table_array.Rows
If CStr(rTemp.Cells(1, column_to_search).Value) = CStr(lookup_value) Then
If Not search_for_multiples Then
sResult = rTemp.Cells(1, column_to_return).Value
bFound = True
Exit For
Else
sResult = sResult & delim_if_multiples & rTemp.Cells(1, column_to_return).Value
bFound = True
iFoundCount = iFoundCount + 1
End If
End If
Next rTemp
Else
For Each rTemp In table_array.Rows
If rTemp.Cells(1, column_to_search).Value = lookup_value Then
If Not search_for_multiples Then
sResult = rTemp.Cells(1, column_to_return).Value
bFound = True
Exit For
Else
sResult = sResult & delim_if_multiples & rTemp.Cells(1, column_to_return).Value
bFound = True
iFoundCount = iFoundCount + 1
End If
End If
Next rTemp
End If
If bFound Then
Select Case iFoundCount
Case 0
vlookup2 = sResult
Case 1
vlookup2 = Mid(sResult, 2)
Case Else
vlookup2 = CStr(iFoundCount) & " found" & sResult
End Select
Else
vlookup2 = result_if_not_found
End If
End Function
For an example, here is some dummy data.
NOTE: grape and pear both have a 1, but for grape, it was entered as a string.
Here are the results:
Based on the following general equation:
I hope you find this useful, or that it give you some ideas for a script of your own.
Thats really impressive. I like how the code can return an array. So I have a big confession, I don't know VBA. I do know DAX and a bit of M, which are new languages used in Excel. but I never learned VBA.
Now following, I know I will learn some neat tricks from you :-)
Honestly the best way to learn VBA is to record some macros and review how Excel codes the actions, since VBA is an Object-oriented, event-based language. The way I build Excel subs is somewhat the same everytime; start with the process you want to automate and the end-goal and have VBA conduct the necessary actions to complete that action: "Take this data and put it here", "Count this column or number of rows and return this value".
Learning the procedure conventions and best-practices is the main topic to learn and you will pick up the different objects and events as you get more experience. I've got some great VBA examples on my site if you'd like to check them out.
But I'm the opposite, I'm beginning to learn DAX and M!
DAX and M rock. I don't know VBA ( and want to find the time to learn it) but I do know DAX and a little M.
M and power query can remove the need for many VBA automation tasks, but it can not do everything VBA can do
In that case, it’s time for some basic VBA exercises.
Here's the first step...
https://steemit.com/hive-102332/@gikitiki/excel-how-to-open-the-vba-editor