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

 

[Scott T]

You are comparing a range to a single value. I do not have your WB in front of me. what exactly are in the cells?

 

[Scott T]

I think this is what you want

Dim findvalue As Range
Set findvalue = Sheets("Score").Range("F13:T21").Find(Range("N2"), LookIn:=xlValues)
If Not findvalue Is Nothing Then Range("AD16").Interior.Color = RGB(255, 255, 0)

 

[jarhead58]

I think this is what you want

Dim findvalue As Range
Set findvalue = Sheets("Score").Range("F13:T21").Find(Range("N2"), LookIn:=xlValues)
If Not findvalue Is Nothing Then Range("AD16").Interior.Color = RGB(255, 255, 0)

That works but I am going to have different scenarios and thats a lot to try to code in. Is it possible to use this type of code in a Select Case?

 

[Scott T]

I don't think Select Case would work here.

If they are all like this then you can just copy it and change the cell to look for, O2 instead of N2 and which cell to color, and color value like.

Dim findvalue As Range
Set findvalue = Sheets("Score").Range("F13:T21").Find(Range("N2"), LookIn:=xlValues)
If Not findvalue Is Nothing Then Range("AD16").Interior.Color = RGB(255, 255, 0)

Set findvalue = Sheets("Score").Range("F13:T21").Find(Range("O2"), LookIn:=xlValues)
If Not findvalue Is Nothing Then Range("AD17").Interior.Color = RGB(255, 205, 0)

 

[jarhead58]

Once again, everything works great! Ii was just trying to get too fancy I think, trying to show the men on base and advance them after a hit but there are too many variables to deal with lol!! Thank you so much for your patience and help!! I will post any further questions on the main forum. I don't want to keep bothering you with things that others can answer as well!! Have a great day!!

 

[Scott T]

It would take up some space but you put check boxes in the cells. One for each base and manual check or uncheck the boxes to show which base has a runner.

 

[jarhead58]

It would take up some space but you put check boxes in the cells. One for each base and manual check or uncheck the boxes to show which base has a runner.

I actually set up an area with black background and setup conditional formatting depending on a number entered into the "base"/cell. It can go from yellow,man on, to no fill, empty base. Its manual but easier to show where the men advance to!

 

[jarhead58]

Hey Scott,

I figured I would reach out to you since you're familiar with the basic structure and understanding of what I am doing with the file. Here is a link to an updated copy, https://www.dropbox.com/s/8s9eu69b74f1lo0/Scorecardtemp.xlsm?dl=0 . One thing that I didn't take into consideration was the fact that, what happens when the batting order, from a good inning, ends up going around again? In other words, the battery comes back to the batter who began the inning and is up for the 2nd time that inning. What would be the best way to be able to record that at bat without losing the existing data? This is beyond my scope of knowledge, which is relatively basic anyway! Ifyou feel that you have the time and can help, it would be very much appreciated. If not, I will repost it to the forum as a new subject. TIA

 

[Scott T]

You would need to record that in different cells. Maybe below your existing lineup you could repeat the lineup, you would need to change the ranges in your code to account for this, you could put the second at bat for a player. So you have the normal batting order in A12:A20 if they bat around in A21 and below you would enter the players that had another at bat. I did not look at the updated sheet but in the older sheets you did not account for a fielders choice.

Book1
	A
11	player
12	Player1
13	Player2
14	Player3
15	Player4
16	Player5
17	Player6
18	Player7
19	Player8
20	Player9
21	Player1
Sheet3

Cell Formulas
Range	Formula
A21	=A12

 

[jarhead58]

Lol, yeah, I actually did account for the 'fielders choice' in the new version. I honestly didn't think about that until I actually started trying it out, ooops, lol!! I will give that a shot and see how that works out. Thanks again, I really do appreciate your help!! Thanks!!