For Each Next

  • Thread starter Thread starter Vacuum Sealed
  • Start date Start date
V

Vacuum Sealed

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.
 
"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
 
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
 
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
 
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.
Click to expand...
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.
 
Vacuum Sealed formulated the question :
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.
Click to expand...

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?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
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
Click to expand...
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.
 
Vacuum Sealed formulated the question :
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.
Click to expand...

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?
Click to expand...



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.
 
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.
Click to expand...

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?
Click to expand...



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.
Click to expand...

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.
 
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.
Click to expand...

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?
Click to expand...



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.
Click to expand...



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.
 
Vacuum Sealed was thinking very hard :
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?
Click to expand...



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.
Click to expand...



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.
Click to expand...


I'm thinking that you might want to build an array with the bonus
scores and 'dump' the results into the tscore range AFTER all updates
are done. Then update the totals column! This would eliminate having to
walk through the range each time because you can iterate the array way
lots faster than reading/writing the range.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
GS was thinking very hard :
I'm thinking that you might want to build an array with the bonus scores and
'dump' the results into the tscore range AFTER all updates are done. Then
update the totals column! This would eliminate having to walk through the
range each time because you can iterate the array way lots faster than
reading/writing the range.
Click to expand...

I see, also, that you're iterating entire columns for the tscore range
when you should only be iterating values inside the used area of that
range on the worksheet.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Vacuum Sealed was thinking very hard :
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?



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.
Click to expand...



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.
Click to expand...


I'm thinking that you might want to build an array with the bonus scores
and 'dump' the results into the tscore range AFTER all updates are done.
Then update the totals column! This would eliminate having to walk
through the range each time because you can iterate the array way lots
faster than reading/writing the range.
Click to expand...

That would be a good idea if the scoreboard was only view at the end of
the event, alas it is view by all team participants on a real-time basis.

A little background may help. This scoreboard is to track tema scores
for trivia nights which is on display the whole time giving each team an
open view to every teams position in the event.

I managed to get it close to how I would like it, just need to fix the
last section of code where the sum(i, tScore) is calced for TTotal.

I have been getting some interesting value totals, in that each cell
seems to equal 75, when in 1 (i, tScore).Value should = 47 and the next
should be 26, so I have no idea where the extra values 28 & 49
respectively are eminating from. I thought it might be evaluating the
entire range but the sample data's value only equates to 73.

The only other issue I have is that Even though I am setting the
condition for Column A (tName) not being empty, it still places a value
in tTotal.

Here is what I have down til now, I feel it just need that finite bit of
tweeking to polish it off.

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

Set tName = Sheets("Scoreboard").Range("A2:A32").Cells
Set tScore = Sheets("Scoreboard").Range("B2:I32").Cells
Set tTotal = Sheets("Scoreboard").Range("J2:J32").Cells

For i = 2 To 32

If Not IsEmpty(tName(i, 1)) Then

For Each Cell In tScore
If Cell.Interior.Color = 15773696 Then
With Cell
.Value = .Value * 2
.Interior.Color = 65535
End With
End If
Next

End If

For j = 2 To 32

If Not IsEmpty(tName(j, 1)) Then
With tName.Offset(0, 9)
.Value = WorksheetFunction.Sum(j, tScore)
End With
End If
Next

Next

End Sub


Thank you again Garry

Regards
Mick.
 
Ok. The fact remains that the totals are the sum of the cells that
contain the bonus score PLUS the non-bonus score cells. I think this
would be better to handle via worksheet formulas in the totals column,
and leave the bonus score calc to VBA so the totals column auto-updates
after bonus scores are calced.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ok. The fact remains that the totals are the sum of the cells that
contain the bonus score PLUS the non-bonus score cells. I think this
would be better to handle via worksheet formulas in the totals column,
and leave the bonus score calc to VBA so the totals column auto-updates
after bonus scores are calced.
Click to expand...
Garry

I tried your suggestion, and yes it does make it simpler, but then I run
into the next hurdle which is the sorting of the tTotal column.

If I use the following:

Range("A1:J32").Sort Key1:=Range("J1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal

It places all the blank rows at the top and the rows with the scores end
up along the bottom. I can only assume the sort detects the nested
formula in the cells and sorts accordingly.

I even had another crack at the Sum(tTotal) syntax and it summed a
different value again...lol....:-), this time around it equated to 46
using the following SumIF().

For j = 2 To 32
If Not IsEmpty(tName(j, 1)) Then
With tTotal
..Value = Application.WorksheetFunction.SumIf(tName, ">""", tScore)
End With
End If
Next j

It still keeps placing values in cells along (j, tTotal) even though (j,
tName) = "".

I'm confused by the use of the following and why it throughs up an
error: Type Mismatch

For j = 2 To 32
If Not IsEmpty(Cells(j, tName)) Then
With Cells(j, tTotal)
..Value = Application.WorksheetFunction.SumIf(Cells(j, tName), "" > """",
Cells(j, tScore))
End With
End If
Next j


Happy to keep looking at the nested cell formulas, but need to get the
sort issue straightened out so that the highest score should be in "J2"
and each lower consecutive score should be in descending order from
there down.

As always, thank you heaps for your time and consideration with this.

Regards
Mick.
 
Vacuum Sealed submitted this idea :
Happy to keep looking at the nested cell formulas, but need to get the sort
issue straightened out so that the highest score should be in "J2" and each
lower consecutive score should be in descending order from there down.
Click to expand...


The formula should return zero if there's no scores to sum. This will
sort properly as apposed to forcing the cells to be "" if the sum is
zero. You can use CF to hide the zeros in the totals column by making
the font color match the interior color.

--
Garry

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


The formula should return zero if there's no scores to sum. This will
sort properly as apposed to forcing the cells to be "" if the sum is
zero. You can use CF to hide the zeros in the totals column by making
the font color match the interior color.
Click to expand...
Thanks for your patience Garry

I decided to use the nested formula and a Custom Foramt that highlighted
the tTotal cell font in bold that has a score > 0.

Cheers
Mick.
 
Back
Top