For Each Next
 

Hi all

Still can't get my head in the right place regarding the subject matter.

I am trying to set up a scoreboard of sorts.

The idea is that if there is a Team Name ( tName ) in Column A then look
at the Team Score ( tScore ) range and for every Cell in ( tScore )
that's back color = 65535 then that Cells value is doubled.

Then the Team's Total will be a Sum of the row that fits within the (
tScore ) range.

the below falls into the N.Q.R. category

Sub Update_Score()

Dim tScore As Range, tTotal As Range
Dim tName As Range
Dim i As Integer

For i = 2 To 32

Set tName = Sheets("Scoreboard").Range("A:A")
Set tScore = Sheets("Scoreboard").Range("B:I")
Set tTotal = Sheets("Scoreboard").Range("J:J")

If Cells(i, tName).Value < "" Then
For Each Cell In tScore
If Cells(i, tScore).Interior.Color = 65535 Then
Cells(i, tScore).Value = Cells(i, tScore).Value * 2
Cells(i, tTotal).Value = WorksheetFunction.Sum(i, tScore)
Next Cell
Else
Exit For
End If
End If

Next i

End Sub

As always your assistance is most appreciated

TIA
Mick.

For Each Next
 

"Cell" isn't declared - use "Option Explicit" as the first line in the module.

For Each Cell in Sheets("Scoreboard").Range("B:I") - returns a Column not a Cell.

For Each Cell in Sheets("Scoreboard").Range("B2:I2").Cells - returns a Cell
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)




"Vacuum Sealed"
wrote in message nd.com...
Hi all

Still can't get my head in the right place regarding the subject matter.

I am trying to set up a scoreboard of sorts.

The idea is that if there is a Team Name ( tName ) in Column A then look at the Team Score (
tScore ) range and for every Cell in ( tScore ) that's back color = 65535 then that Cells value is
doubled.

Then the Team's Total will be a Sum of the row that fits within the ( tScore ) range.

the below falls into the N.Q.R. category

Sub Update_Score()

Dim tScore As Range, tTotal As Range
Dim tName As Range
Dim i As Integer

For i = 2 To 32

Set tName = Sheets("Scoreboard").Range("A:A")
Set tScore = Sheets("Scoreboard").Range("B:I")
Set tTotal = Sheets("Scoreboard").Range("J:J")

If Cells(i, tName).Value < "" Then
For Each Cell In tScore
If Cells(i, tScore).Interior.Color = 65535 Then
Cells(i, tScore).Value = Cells(i, tScore).Value * 2
Cells(i, tTotal).Value = WorksheetFunction.Sum(i, tScore)
Next Cell
Else
Exit For
End If
End If

Next i

End Sub

As always your assistance is most appreciated

TIA
Mick.

For Each Next
 

Could look something like this...
'---
Sub More_Scores()
Dim i As Long
Dim Cell As Range
Dim tName As Range
Dim tScore As Range
Dim tTotal As Range

Set tName = Sheets("Scoreboard").Range("A:A").Cells
Set tTotal = Sheets("Scoreboard").Range("J:J").Cells

For i = 2 To 23
If Not IsEmpty(tName(i, 1)) Then
Set tScore = tName(i, 1).Offset(0, 1).Resize(1, 8).Cells
For Each Cell In tScore
'do stuff
Next
End If
Next
End Sub
'---
Jim Cone






"Jim Cone"
wrote in message ...
"Cell" isn't declared - use "Option Explicit" as the first line in the module.

For Each Cell in Sheets("Scoreboard").Range("B:I") - returns a Column not a Cell.

For Each Cell in Sheets("Scoreboard").Range("B2:I2").Cells - returns a Cell
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)







"Vacuum Sealed"
wrote in message nd.com...
Hi all

Still can't get my head in the right place regarding the subject matter.

I am trying to set up a scoreboard of sorts.

The idea is that if there is a Team Name ( tName ) in Column A then look at the Team Score (
tScore ) range and for every Cell in ( tScore ) that's back color = 65535 then that Cells value
is doubled.

Then the Team's Total will be a Sum of the row that fits within the ( tScore ) range.

the below falls into the N.Q.R. category

Sub Update_Score()

Dim tScore As Range, tTotal As Range
Dim tName As Range
Dim i As Integer

For i = 2 To 32

Set tName = Sheets("Scoreboard").Range("A:A")
Set tScore = Sheets("Scoreboard").Range("B:I")
Set tTotal = Sheets("Scoreboard").Range("J:J")

If Cells(i, tName).Value < "" Then
For Each Cell In tScore
If Cells(i, tScore).Interior.Color = 65535 Then
Cells(i, tScore).Value = Cells(i, tScore).Value * 2
Cells(i, tTotal).Value = WorksheetFunction.Sum(i, tScore)
Next Cell
Else
Exit For
End If
End If

Next i

End Sub

As always your assistance is most appreciated

TIA
Mick.

For Each Next
 

Mick,
What is the criteria for setting the Team Score cells fill color to
yellow? I'm asking because a worksheet function might be more efficient
and so perhaps using SUMIF() in the Team Total column and specifying
the same criteria used to 'flag' the cells might be better than a VBA
solution, AND the totals will auto-update when the criteria applies.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

For Each Next
 

On 26/02/2012 9:47 AM, GS wrote:
Mick,
What is the criteria for setting the Team Score cells fill color to
yellow? I'm asking because a worksheet function might be more efficient
and so perhaps using SUMIF() in the Team Total column and specifying the
same criteria used to 'flag' the cells might be better than a VBA
solution, AND the totals will auto-update when the criteria applies.

Hi Garry

I have a separate code for that.

Each team has the opportunity to play a wildcard round which gives them
a double bonus round.

I also have another code to undo the cell color just in case the team
changes their mind or the score-keeper sets the wrong cell.

Regards
Mick.

For Each Next
 

Vacuum Sealed formulated the question :
On 26/02/2012 9:47 AM, GS wrote:
Mick,
What is the criteria for setting the Team Score cells fill color to
yellow? I'm asking because a worksheet function might be more efficient
and so perhaps using SUMIF() in the Team Total column and specifying the
same criteria used to 'flag' the cells might be better than a VBA
solution, AND the totals will auto-update when the criteria applies.

Hi Garry

I have a separate code for that.

Each team has the opportunity to play a wildcard round which gives them a
double bonus round.

I also have another code to undo the cell color just in case the team changes
their mind or the score-keeper sets the wrong cell.

Regards
Mick.

Ok. Why I was asking is so I could determine whether it my be better to
implement CF to handle the coloring based on some criteria entered as a
formula. Then the totals column could auto-calc based on that same
criteria.

Is there any provision in your worksheet design to restrict entry to
the wrong cell[s]?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

For Each Next
 

On 26/02/2012 3:31 AM, Jim Cone wrote:
Could look something like this...
'---
Sub More_Scores()
Dim i As Long
Dim Cell As Range
Dim tName As Range
Dim tScore As Range
Dim tTotal As Range

Set tName = Sheets("Scoreboard").Range("A:A").Cells
Set tTotal = Sheets("Scoreboard").Range("J:J").Cells

For i = 2 To 23
If Not IsEmpty(tName(i, 1)) Then
Set tScore = tName(i, 1).Offset(0, 1).Resize(1, 8).Cells
For Each Cell In tScore
'do stuff
Next
End If
Next
End Sub
'---
Jim Cone






"Jim
wrote in message ...
"Cell" isn't declared - use "Option Explicit" as the first line in the module.

For Each Cell in Sheets("Scoreboard").Range("B:I") - returns a Column not a Cell.

For Each Cell in Sheets("Scoreboard").Range("B2:I2").Cells - returns a Cell
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)







"Vacuum
wrote in message nd.com...
Hi all

Still can't get my head in the right place regarding the subject matter.

I am trying to set up a scoreboard of sorts.

The idea is that if there is a Team Name ( tName ) in Column A then look at the Team Score (
tScore ) range and for every Cell in ( tScore ) that's back color = 65535 then that Cells value
is doubled.

Then the Team's Total will be a Sum of the row that fits within the ( tScore ) range.

the below falls into the N.Q.R. category

Sub Update_Score()

Dim tScore As Range, tTotal As Range
Dim tName As Range
Dim i As Integer

For i = 2 To 32

Set tName = Sheets("Scoreboard").Range("A:A")
Set tScore = Sheets("Scoreboard").Range("B:I")
Set tTotal = Sheets("Scoreboard").Range("J:J")

If Cells(i, tName).Value< "" Then
For Each Cell In tScore
If Cells(i, tScore).Interior.Color = 65535 Then
Cells(i, tScore).Value = Cells(i, tScore).Value * 2
Cells(i, tTotal).Value = WorksheetFunction.Sum(i, tScore)
Next Cell
Else
Exit For
End If
End If

Next i

End Sub

As always your assistance is most appreciated

TIA
Mick.


Hi Jim

Thank you for your assist on this.

This is what I have, and I still can't get it to step all the way
through as it halts with a "Type Mismatch" at this section.

With Cells(i, tTotal).Value
..Sum(i, tScore).Value
End With

also, the Cells that do have colored backgrounds are not doubling their
value even though it steps through this section with no errors flagged.

With tScore.Cells.Interior.Color = 65535
Cells(i, Cell).Value = Cells(i, Cell).Value * 2
End With

Dim i As Long
Dim Cell As Range
Dim tName As Range
Dim tScore As Range
Dim tTotal As Range

Set tName = Sheets("Scoreboard").Range("A:A").Cells
Set tTotal = Sheets("Scoreboard").Range("J:J").Cells

For i = 2 To 23

If Not IsEmpty(tName(i, 1)) Then
Set tScore = tName(i, 1).Offset(0, 1).Resize(1, 8).Cells

For Each Cell In tScore
With tScore.Cells.Interior.Color = 65535
Cells(i, Cell).Value = Cells(i, Cell).Value * 2
End With
With Cells(i, tTotal).Value
.Sum(i, tScore).Value
End With
Next

End If

Next

I tried using an If statement, but that too resulted in error.

Thanks again
Mick.

For Each Next
 

On 26/02/2012 12:31 PM, GS wrote:
Vacuum Sealed formulated the question :
On 26/02/2012 9:47 AM, GS wrote:
Mick,
What is the criteria for setting the Team Score cells fill color to
yellow? I'm asking because a worksheet function might be more efficient
and so perhaps using SUMIF() in the Team Total column and specifying the
same criteria used to 'flag' the cells might be better than a VBA
solution, AND the totals will auto-update when the criteria applies.

Hi Garry

I have a separate code for that.

Each team has the opportunity to play a wildcard round which gives
them a double bonus round.

I also have another code to undo the cell color just in case the team
changes their mind or the score-keeper sets the wrong cell.

Regards
Mick.

Ok. Why I was asking is so I could determine whether it my be better to
implement CF to handle the coloring based on some criteria entered as a
formula. Then the totals column could auto-calc based on that same
criteria.

Is there any provision in your worksheet design to restrict entry to the
wrong cell[s]?



Hi Garry

No provisions for error trapping at all as I was trying to make it quick
and simple, having said that, I just realised a flaw in my logic when
applying the bonus round.

If I keep the current structure, each time the score-keeper hits the
update Score button at the end of each round, it will continue to double
all cells with a yellow background.

I am very happy if you have an alternative line of thinking on this
please Garry.

Regards
Mick.

For Each Next
 

On 26/02/2012 1:05 PM, Vacuum Sealed wrote:
On 26/02/2012 12:31 PM, GS wrote:
Vacuum Sealed formulated the question :
On 26/02/2012 9:47 AM, GS wrote:
Mick,
What is the criteria for setting the Team Score cells fill color to
yellow? I'm asking because a worksheet function might be more efficient
and so perhaps using SUMIF() in the Team Total column and specifying
the
same criteria used to 'flag' the cells might be better than a VBA
solution, AND the totals will auto-update when the criteria applies.

Hi Garry

I have a separate code for that.

Each team has the opportunity to play a wildcard round which gives
them a double bonus round.

I also have another code to undo the cell color just in case the team
changes their mind or the score-keeper sets the wrong cell.

Regards
Mick.

Ok. Why I was asking is so I could determine whether it my be better to
implement CF to handle the coloring based on some criteria entered as a
formula. Then the totals column could auto-calc based on that same
criteria.

Is there any provision in your worksheet design to restrict entry to the
wrong cell[s]?



Hi Garry

No provisions for error trapping at all as I was trying to make it quick
and simple, having said that, I just realised a flaw in my logic when
applying the bonus round.

If I keep the current structure, each time the score-keeper hits the
update Score button at the end of each round, it will continue to double
all cells with a yellow background.

I am very happy if you have an alternative line of thinking on this
please Garry.

Regards
Mick.
Garry

Just had an idea, I can change the original color to say a Light Blue,
then, when the Score-Keeper updates the total the cell turns yellow thus
eliminating it from being re-doubled.

Your thoughts
Mick.

For Each Next
 

On 26/02/2012 1:05 PM, Vacuum Sealed wrote:
On 26/02/2012 12:31 PM, GS wrote:
Vacuum Sealed formulated the question :
On 26/02/2012 9:47 AM, GS wrote:
Mick,
What is the criteria for setting the Team Score cells fill color to
yellow? I'm asking because a worksheet function might be more efficient
and so perhaps using SUMIF() in the Team Total column and specifying
the
same criteria used to 'flag' the cells might be better than a VBA
solution, AND the totals will auto-update when the criteria applies.

Hi Garry

I have a separate code for that.

Each team has the opportunity to play a wildcard round which gives
them a double bonus round.

I also have another code to undo the cell color just in case the team
changes their mind or the score-keeper sets the wrong cell.

Regards
Mick.

Ok. Why I was asking is so I could determine whether it my be better to
implement CF to handle the coloring based on some criteria entered as a
formula. Then the totals column could auto-calc based on that same
criteria.

Is there any provision in your worksheet design to restrict entry to the
wrong cell[s]?



Hi Garry

No provisions for error trapping at all as I was trying to make it quick
and simple, having said that, I just realised a flaw in my logic when
applying the bonus round.

If I keep the current structure, each time the score-keeper hits the
update Score button at the end of each round, it will continue to double
all cells with a yellow background.

I am very happy if you have an alternative line of thinking on this
please Garry.

Regards
Mick.


OK

I got the wildcard scoring fixed with the following:

I now use a Light Blue to highlight those teams who are playing their
wildcard round, when the scoreboard is updated it doubles the score for
that round and changes the cell color to Yellow which allows the code to
step through them without re-applying the wildcard double value.

So far so good to this point.

I should outline for the scoreboard total update process I could simply
use cell formulas in the total column, but I was trying not to leave
nested formulas there in case the User/Score-Keeper deleted it.

This brings me to the next hurdle.

For Each Cell In tTotal
Cell.Value = WorksheetFunction.SumProduct((tName) * (tScore))
Next

This throws up a Type Mismatch error.

So close, but Yet....

Mick.