Excel Wish List - HOT DAMN!

There's an old saying in the news business: "If you don't like the news out there, go make some of your own."

I decided to apply that same line of reasoning to Excel functions. Back in a previous blog post, I had asked out loud if certain functions could be included (see Microsoft Office Wish List, the section under Excel):

1. Count instances of a string (how many times does the letter "e" show up in "Geoff"
2. Find the nth instance of a string (find where the 3rd "g" shows up in a cell's text)
3. An ORDINAL function
4. A work hours elapsed function
5. A VLOOKUP function with an additional argument for what instance you're looking for

Well, I went 4 for 5 today. I've been able to pull off all of them but #4.

The code follows:

For #1, my challenge was to count how many times a letter or letters show up in a cell's text.

The code:

Public Function CountText(strTexttoCount As String, strTextToFind As String)
Dim strTextoFindLen As String
Dim intTextToCountLen As Integer
Dim intTextToFindLen As Integer
Dim g As Integer
intTextToCountLen = Len(strTexttoCount)
intTextToFindLen = Len(strTextToFind)
For g = 1 To intTextToCountLen
If Mid(strTexttoCount, g, intTextToFindLen) = strTextToFind Then
CountText = CountText + 1
End If
Next
End Function

This one proved to be pretty straightforward; pass the cell's text and the text to find as string variables to a function, then loop through the cell's text letter by letter. If the text to find is there, then increment a counter by 1. When you're all done looping, what is the counter at?

#2 was the Nth instance of a string - find what position in a string does the 3rd g, the fourth f, the second T show up?

The code:
Public Function ModeFind(strTexttoCount As String, strTextToFind As String, intInstancetoCount As Integer)
Dim strTextoFindLen As String
Dim intTextToCountLen As Integer
Dim intTextToFindLen As Integer
Dim intTextOccurrence As Integer
Dim g As Integer
intTextToCountLen = Len(strTexttoCount)
intTextToFindLen = Len(strTextToFind)
For g = 1 To intTextToCountLen
If Mid(strTexttoCount, g, intTextToFindLen) = strTextToFind Then
intTextOccurrence = intTextOccurrence + 1
If intTextOccurrence = intInstancetoCount Then
ModeFind = Len(Left(strTexttoCount, g))
End If

End If
Next
If intTextOccurrence <>
ModeFind = 0
End If
End Function

I recycled some code from the previous function to make this one happen, but same idea - loop through the cell's text, and keep looking for the "find" text. Count how many times you find it - when that counter is equal to the instance number, then get the position number, and return it.

#3 was the ORDINAL function idea. This one was actually the easiest of the four that I pulled off; code follows:

Public Function Ordinal(lngNumberToOrdinal As Long)
Select Case Right(lngNumberToOrdinal, 1)
Case 1
Ordinal = lngNumberToOrdinal & "st"
Case 2
Ordinal = lngNumberToOrdinal & "nd"
Case 3
Ordinal = lngNumberToOrdinal & "rd"
Case Else
Ordinal = lngNumberToOrdinal & "th"
End Select
End Function

All I had to do was get the leftmost digit of the number passed to the function, and then figure out what it was. Based on what it was, I added the appropriate suffix using a Select...Case statement.

My #4 challenge, I haven't worked on yet. I found some code from a guy called Jimmy Peña that looks promising...if I get his code to work, I'll let you know.

#5 was a bit of a bugger. My method is inelegant, clunky, and effective:

Public Function vlookupRow(rngRangeToSearch As String, varTexttoFind, lngRow As Long, lngColumn As Long)
Call FindBillyBrown35(rngRangeToSearch, varTexttoFind, lngRow, lngColumn)
vlookupRow = vOurResult
End Function


'******************************************************************************************
'This is a heavily modified version of some code from Dave Hawley of OzGrid.com that
'I've modified and re-purposed for my own function
'give credit where credit is due
'original can be found at http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm
'THANK YOU MR. HAWLEY!
'******************************************************************************************
Sub FindBillyBrown35(rngRangeToSearch As String, varTexttoFind, lngRow As Long, lngColumn As Long)

Dim rFoundIt As Range
Dim iLoop As Integer
Dim intCount As Integer
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
With Sheet1.Range(rngRangeToSearch)
'Set variable to start search from
Set rFoundIt = .Cells(1, 1)
'Loop no more times that the name occurs
For iLoop = 0 To WorksheetFunction.CountIf(Range(rngRangeToSearch), varTexttoFind)

'ReSet variable to found occurence of name. Next loop search _
will start AFTER this Set cell.
Set rFoundIt = .Find(What:=varTexttoFind, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
intCount = intCount + 1
'Check if it's the right one.
If intCount = lngRow Then
vOurResult = rFoundIt.Offset(0, lngColumn).Value
Exit For 'Leave loop
End If

Next iLoop
End With 'Sheet1.Range("Data")

If IsEmpty(vOurResult) Then
vOurResult = "#N/A"
End If
End Sub

I did the call to the Sub so that I could debug this stupid thing; I was having a devil of a time getting the range to evaluate properly. What I found, after all was said and done, was that if I quoted the range in the function itself (e.g., =VLOOKUPRow("A1:C6","apple",2,2) then I could get it to work perfectly. I'm not thrilled with that solution, but it works, so maybe it's time not to look a gift horse in the mouth.

Well, it's a start...just goes to show that if you want to throw down the gauntlet, be prepared to pick it back up, as well. Let that be a lesson to you. Until the next lesson, be well.