Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
match function with text and numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
match function with text and numbers
Gary''s Student wrote: Always use a string Dim s as String s = "AA" call LookupNLHand(s) s="99" call LookupNLHand(s) -- Gary's Student Using your examples, "AA" works fine but "99" throws error 2042 on the vlookup |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
match function with text and numbers
Gary''s Student wrote: 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. -- Gary's Student "sugargenius" wrote: Gary''s Student wrote: Always use a string Dim s as String s = "AA" call LookupNLHand(s) s="99" call LookupNLHand(s) -- Gary's Student Using your examples, "AA" works fine but "99" throws error 2042 on the vlookup 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH search with text and numbers | Excel Worksheet Functions | |||
rounding numbers for match function | Excel Worksheet Functions | |||
rounding numbers for match function | Excel Worksheet Functions | |||
How do I set up an IF function with text and numbers? | Excel Worksheet Functions | |||
Match Function Problem - Won't Find Certain Numbers | Excel Discussion (Misc queries) |