match function with text and numbers

[sugargenius]

I'm building a little app that takes a NL Hole Em starting hand and
outputs its rank

I have a named range that looks like

AA 1 Early Tight 1
KK 1 Early Tight 2
QQ 1 Early Tight 3
JJ 1 Early Tight 4
AKS 1 Early Tight 5
TT 2 Early Tight 6
AQS 2 Early Tight 7
AJS 2 Early Tight 8
AK 2 Early Tight 9
KQS 2 Early Tight 10
ATS 3 Early Tight 11
KJS 3 Early Tight 12
AQ 3 Early Tight 13
99 3 Early Tight 14
QJS 3 Early Tight 15
K10S 3 Early Tight 16
88 4 Early Tight 17
Q10S 4 Early Tight 18
A9S 4 Early Tight 19
AJ 4 Early Tight 20
J10S 4 Early Tight 21
KQ 4 Early Tight 22
A8S 4 Early Tight 23
AT 4 Early Tight 24

Sub LookupNLHand(sStartingHand As String)
Dim res As Variant, Hand As HoldemHand, rngLookup As Range, res2 As
Variant
Set rngLookup = Range("STARTING_HANDS_LOOKUP")
'res = Application.VLookup(sStartingHand,
Range("STARTING_HANDS_LOOKUP"), 5, False)
res = Application.VLookup(sStartingHand, rngLookup, 5, False)

If IsError(res) Then
MsgBox "Could not locate that starting hand!"
Exit Sub
End If

With Hand
.iRank = res
.iGroup = rngLookup(res, GROUP_COL)
.sPosition = rngLookup(res, POSITION_COL)
.sStrategy = rngLookup(res, STRATEGY_COL)
End With
End Sub

If input a string like "AA", it works. But if input a number like
"106", i get Error 2042

I tried it with MATCH too, but ran into same problem.

What's the best way to lookup a value that could be a string or a
number?

thanks
woody

 

[sugargenius]

Always use a string

Dim s as String
s = "AA"
call LookupNLHand(s)
s="99"
call LookupNLHand(s)

Using your examples, "AA" works fine but "99" throws error 2042 on the
vlookup

 

[sugargenius]

I got you data to work. msgbox(res) returned 1 for "AA" and returned 14 for
"99"

Just be sure that the 99 is really a text value. I copied and pasted right
from your posting. When I expanded the text-to-columns, I told the wizard to
treat the first column as text. That way the 99 is just as "find-able" as
any other string.

This is the only way I could get it to work:

Sub LookupNLHand(sStartingHand As Variant)
Dim res As Variant, Hand As HoldemHand, rngLookup As Range, sMsg As
String
Set rngLookup = Range("STARTING_HANDS_LOOKUP")

If Not IsNumeric(sStartingHand) Then
'res = Application.VLookup(sStartingHand, rngLookup, 5, False)
res = Application.Match(sStartingHand, rngLookup)
Else
res = Application.Match(CInt(sStartingHand), rngLookup)
End If

If IsError(res) Then
MsgBox "Could not locate that starting hand!"
Exit Sub
End If

With Hand
.iRank = res
.iGroup = rngLookup(res, GROUP_COL)
.sPosition = rngLookup(res, POSITION_COL)
.sStrategy = rngLookup(res, STRATEGY_COL)
End With

sMsg = "Your hand is ranked " & Hand.iRank & vbCrLf & _
"It is in group " & Hand.iGroup & vbCrLf & _
"You should only play this if you are in " & Hand.sPosition & "
position" & vbCrLf & _
"Or, playing " & Hand.sStrategy

MsgBox sMsg

End Sub