Recommend
1 
 Thumb up
 Hide
5 Posts

BoardGameGeek» Forums » Everything Else » Chit Chat

Subject: Excel Macro help needed rss

Your Tags: Add tags
Popular Tags: [View All]
Chris
United States
Ronkonkoma
New York
flag msg tools
badge
Avatar
mbmbmbmbmb
So, a friend has a soccer game that was made into an Excel version, and there's ONE error with it (and he asked me to fix it, if I could).

Basically, when a player gets a "Red" card, it doesn't record that on the game scoresheet. It DOES when the player gets a "Yellow" card. Seems simple to fix, but I've been tinkering and tinkering, and I can't see why it's not working

Here is the text of the macro (I believe) - anyone wanna take a shot?





Sub REFseq()
Dim Poss As Integer, Refcell$, Txt$, msg As Variant, RefCelStat$, Nom$

ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False



Range("RefD").Value = Range("RefDecision").Value

If Averto = True Then Range("RefD").Value = "Lectured"

'RED________________________________________________
If Range("RefD").Value = "Red card" Then
ActiveSheet.Shapes("RedC").Visible = True
Poss = Range("Pos").Value

Sheets("Teams").Select
If WhoFouls = 1 Then
Refcell$ = "I" & CStr(2 + NumListH)
Nom$ = Range(Refcell$).Value
NumligFH = NumligFH + 1
RefCelStat$ = "BL" & CStr(78 + NumligFH)

Else
Refcell$ = "I" & CStr(29 + NumListV)
Nom$ = Range(Refcell$).Value
NumLigFV = NumLigFV + 1
RefCelStat$ = "BO" & CStr(78 + NumLigFV)

End If
Sheets("Game").Select
Range(RefCelStat$).Value = Nom$ & " [R]"

Txt$ = Nom$ & " is RedCarded! He must leave the game..."
msg = MsgBox(Txt$, , "Fast Dice Auto")
End If
'Yellow ****** __________________________________________________________
If Range("RefD").Value = "Booked *" Then
ActiveSheet.Shapes("BookedIf").Visible = True

If WhoFouls = 1 Then
Refcell$ = "I" & CStr(2 + NumListH)

Else
Refcell$ = "I" & CStr(29 + NumListV)

End If
Sheets("Teams").Select
If Right(Range(Refcell$).Value, 1) <> "#" Then
Nom$ = Range(Refcell$).Value
Sheets("Game").Select
Range("RefD").Value = "Booked"
Else
Sheets("Game").Select
Range("RefD").Value = "Lectured"
End If
End If

'Yellow_________________________________________________________
If Range("RefD").Value = "Booked" Then
ActiveSheet.Shapes("YellowC").Visible = True
Sheets("Teams").Select
If WhoFouls = 1 Then
Refcell$ = "I" & CStr(2 + NumListH)
Nom$ = Range(Refcell$).Value
NumligFH = NumligFH + 1
RefCelStat$ = "BL" & CStr(78 + NumligFH)

If Right(Range(Refcell$).Value, 1) <> "#" Then
Range(Refcell$).Value = Range(Refcell$).Value & " #"
Sheets("Game").Select
Range("RefD").Value = "Booked"
Range(RefCelStat$).Value = Nom$ & "[Y]"
Else
Sheets("Game").Select
Range("RefD").Value = "Booked 2#"
Range(RefCelStat$).Value = Nom$ & "[Y>R]"
Txt$ = "Second yellow card for " & Nom$ & ". He must leave the game..."
msg = MsgBox(Txt$, , "Fast Dice Auto")
End If

Else
Refcell$ = "I" & CStr(29 + NumListV)
Nom$ = Range(Refcell$).Value
NumLigFV = NumLigFV + 1
RefCelStat$ = "BO" & CStr(78 + NumLigFV)

If Right(Range(Refcell$).Value, 1) <> "#" Then
Range(Refcell$).Value = Range(Refcell$).Value & " #"
Sheets("Game").Select
Range("RefD").Value = "Booked"
Range(RefCelStat$).Value = Nom$ & "[Y]"
Else
Sheets("Game").Select
Range("RefD").Value = "Booked 2#"
Range(RefCelStat$).Value = Nom$ & "[Y>R]"
Txt$ = "Second yellow card for " & Nom$ & ". He must leave the game..."
msg = MsgBox(Txt$, , "Fast Dice Auto")
End If


End If

End If
Sheets("report").Range("K" & CStr(NumLig)).Value = Sheets("game").Range("refd")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Bryan Thunkd
United States
Florence
MA
flag msg tools
badge
Avatar
mbmbmbmbmb
I'd have to have a copy of the spreadsheet and some time to play around with it.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Matt
United States
Central Coast
California
flag msg tools
0110100110010110
badge
Avatar
mbmbmbmbmb
Quote:
If Range("RefD").Value = "Red card" Then


I bet this is your problem right here. If you check the values that are put into the referee's cell, I'd wager that you never see "Red card", but that you do see either "Red Card" (note the capitalization) or "RedCard" or something else.

You can make it case insensitive by changing it to:
Quote:
If UCase(Range("RefD").Value) = "RED CARD" Then

but that doesn't help if you have unpredictable spaces in the text.

You could also change it to
Quote:
If Left$(UCase(Range("RefD").Value),3) = "RED" Then

but this would also flag "Red Bird", "Reddit", etc.

If this isn't the problem then I'd probably need to see more.
5 
 Thumb up
1.25
 tip
 Hide
  • [+] Dice rolls
Chris
United States
Ronkonkoma
New York
flag msg tools
badge
Avatar
mbmbmbmbmb
Yes - that was exactly it! Someone else pointed it out (and, I didn't write the macro, and I assumed there was something "formulaic" wrong (and not something as dumb as a call out to a term that didn't exist!

Thanks, both, for your help - sometimes, the answer is so stupidly easy; I didn't even think to start from the first step ......
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Matt
United States
Central Coast
California
flag msg tools
0110100110010110
badge
Avatar
mbmbmbmbmb
Lemur wrote:
Yes - that was exactly it! Someone else pointed it out (and, I didn't write the macro, and I assumed there was something "formulaic" wrong (and not something as dumb as a call out to a term that didn't exist!

Thanks, both, for your help - sometimes, the answer is so stupidly easy; I didn't even think to start from the first step ......


You're welcome! I'll just leave this here... mb

3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.