[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

match function with text and numbers

sugargenius

12/12/2006 11:30:00 PM

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
4 Answers

Gary''s Student

12/12/2006 11:58:00 PM

0

Always use a string

Dim s as String
s = "AA"
call LookupNLHand(s)
s="99"
call LookupNLHand(s)
--
Gary's Student


"sugargenius" wrote:

> 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

12/13/2006 12:24:00 AM

0


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

sugargenius

12/13/2006 1:39:00 PM

0


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

Gary''s Student

12/13/2006 1:58:00 PM

0

You have re-confirmed that using VLOOKUP in VBA can be a pain in the a**. It
seems to be very fussy about the type of search variable.

It is usually easier for me to code in a loop rather than spend time trying
to figure out how to make VLOOKUP() happy.
--
Gary's Student


"sugargenius" wrote:

>
> 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
>
>