countif cell back to 0

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
367
Office Version
  1. 2021
Platform
  1. Windows
How can I return my count to "0" after using this formula
Code:
=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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
Could I use a For Next with this code
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?
 
Upvote 0
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.

Code:
    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.
Code:
Range("F11:T11").ClearContents
Range("F11") = "X"
 
Upvote 0
Code:
[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!!!
 
Upvote 0
You can call it from the other subs for example where curinning is the sub you are calling.

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

Code:
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.
 
Last edited:
Upvote 0
You can call it from the other subs for example where curinning is the sub you are calling.

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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,852
Messages
6,181,408
Members
453,036
Latest member
Koyaanisqatsi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top