countif cell back to 0

[jarhead58]

How can I return my count to "0" after using this formula

=MOD(COUNTIFS(F13:T21,R2)+COUNTIFS(F13:T21,"Ground out")+COUNTIFS(F13:T21,"Fly out")+COUNTIFS(F13:T21,"Foul out")-1,3)+1

when F13:T21 are cleared? TIA

 

[jarhead58]

Here's a new link to the file.

https://www.dropbox.com/s/09r0xwbsv1vg2ev/Scorecard.xlsm?dl=0

 

[Scott T]

I copied it into the M5 cell but it only took it as text. The = sign is there with no spaces before it. Not sure whats going on.

Try going into edit mode by pressing F2 and then past.

 

[jarhead58]

Try going into edit mode by pressing F2 and then past.

Ok, thats got it working except for the reset. I put an X in the next inning, G11 and put a W in it but the count stays at 3.

 

[jarhead58]

Ok, thats got it working except for the reset. I put an X in the next inning, G11 and put a W in it but the count stays at 3.

Found the problem! "x" needs to be removed from the previous inning in order for it to reset.

 

[Scott T]

Yes, forgot to mention that.

 

[jarhead58]

Could I use a For Next with this code

If Range("M5").Value >= 3 Then        Range("F11:T11").ClearContents
        Else
    End If
    Range("F11:T11").Value = "x"

to do what I need; remove 'x' from cell and put 'x' in next cell?

 

[Scott T]

Something like this should move the X. What do you want to happen if the game goes more the 15 innings? the below will display a message box and not move the X since you do not have any more columns.

    Dim x As RangeSet x = Sheets("Score").Range("F11:T11").Find("x", LookIn:=xlValues)
If Range("M5") = 3 And x.Address <> "$T$11" Then
    x.ClearContents
    x.Offset(0, 1) = "X"
ElseIf x.Address = "$T$11" Then
    'what should happen if the game goes more than 15 innings? this will give a mesage box
    MsgBox "this is a long game!"
    
End If

In your clear cells code add this to clear the X and move it back to the 1st inning.

Range("F11:T11").ClearContents
Range("F11") = "X"

 

[jarhead58]

[COLOR=#574123] Dim x As RangeSet x = Sheets("Score").Range("F11:T11").Find("x", LookIn:=xlValues)[/COLOR]If Range("M5") = 3 And x.Address <> "$T$11" Then
    x.ClearContents
    x.Offset(0, 1) = "X"
ElseIf x.Address = "$T$11" Then
    'what should happen if the game goes more than 15 innings? this will give a mesage box [COLOR=#574123]    MsgBox "this is a long game!"[/COLOR]

So should I just put this in the General/Declaration code page? Wife was having neck surgery so I was trying to keep my mind occupied but all is good now!! Love the message btw lol!!!

 

[Scott T]

You can call it from the other subs for example where curinning is the sub you are calling.

Sub flyout()   
 Worksheets("Score").Range("V2").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Call curinning
End Sub

Or you can just add the code directly to each sub

Sub flyout()   
 Worksheets("Score").Range("V2").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Dim x As Range


Set x = Sheets("Score").Range("F11:T11").Find("x", LookIn:=xlValues)
If Range("M5") = 3 And x.Address <> "$T$11" Then
    x.ClearContents
    x.Offset(0, 1) = "X"
ElseIf x.Address = "$T$11" Then
    'what should happen if the game goes more than 15 innings? this will give a mesage box
    MsgBox "this is a long game!"
    
End If
End Sub

Which ever one you choose you will need to add it to each sub that is related to an out, DP, TP, Fly out etc.

If you think you will ever need to modify the code it is best to just call it so you do not have to go to each sub and change it.

 

[jarhead58]

You can call it from the other subs for example where curinning is the sub you are calling.

Sub flyout()   
 Worksheets("Score").Range("V2").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Call curinning
End Sub

Or you can just add the code directly to each sub

Sub flyout()   
 Worksheets("Score").Range("V2").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Dim x As Range


Set x = Sheets("Score").Range("F11:T11").Find("x", LookIn:=xlValues)
If Range("M5") = 3 And x.Address <> "$T$11" Then
    x.ClearContents
    x.Offset(0, 1) = "X"
ElseIf x.Address = "$T$11" Then
    'what should happen if the game goes more than 15 innings? this will give a mesage box
    MsgBox "this is a long game!"
    
End If
End Sub

Which ever one you choose you will need to add it to each sub that is related to an out, DP, TP, Fly out etc.

If you think you will ever need to modify the code it is best to just call it so you do not have to go to each sub and change it.

Hmmm..getting an Invalid outside procedure and it highlights "Set" in the code.