I would like to know if this is possible? I would like to create a
spreadsheet that would have three calls. first cell C5 as a daily score cell,
then have another cell C10 only add all the daily score and count how many
times there was a change in the score, Then have cell C15 take the total of
cell C10 divide the amount of daily score and come up with a average. If I am
barking up the wrong software can you point me in the right direction.
--
Thank You
Glenn

Using the worksheet change macro I put the scores on a worksheet called SCORES.
Column A is the scores and column B get either "Changed" or "Not Changed".
I assumed the 1st entry will always be a change since there was no previous
data.

When you enter a number in cell C5 the worksheet change function
automaticaly gets called.

the number of chaes in C10 is a formula
=COUNTIF(Scores!$B:$B,"Changed")

The results in C15 is
=SUM(Scores!$A:$A)/C10

Sub worksheet_change(ByVal Target As Range)

If Target.Address = "$C$5" Then
With Sheets("Scores")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If LastRow < 1 Or _
Target < "" Then

LastRow = LastRow + 1
End If

.Range("A" & LastRow) = Target.Value
If LastRow = 1 Then
.Range("B1") = "Changed"
Else
If .Range("A" & LastRow) = _
.Range("A" & (LastRow - 1)) Then

.Range("B" & LastRow) = "Not Changed"
Else
.Range("B" & LastRow) = "Changed"
End If
End If
End With
End If

End Sub


"Glenn" wrote:

I would like to know if this is possible? I would like to create a
spreadsheet that would have three calls. first cell C5 as a daily score cell,
then have another cell C10 only add all the daily score and count how many
times there was a change in the score, Then have cell C15 take the total of
cell C10 divide the amount of daily score and come up with a average. If I am
barking up the wrong software can you point me in the right direction.
--
Thank You
Glenn

Joel,

I am a novice at Excel.

When I create Cell C5 I am going to create a Macro.
=COUNTIF(Scores!$B:$B,"Changed")
=COUNTIF(scores!$C:$5,"Changed") ?
I have tried it several ways and haven't had much success. Am I doing
something wrong? Any is deeply appreciated!
--
Thank You
Glenn


"Joel" wrote:

Using the worksheet change macro I put the scores on a worksheet called SCORES.
Column A is the scores and column B get either "Changed" or "Not Changed".
I assumed the 1st entry will always be a change since there was no previous
data.

When you enter a number in cell C5 the worksheet change function
automaticaly gets called.

the number of chaes in C10 is a formula
=COUNTIF(Scores!$B:$B,"Changed")

The results in C15 is
=SUM(Scores!$A:$A)/C10

Sub worksheet_change(ByVal Target As Range)

If Target.Address = "$C$5" Then
With Sheets("Scores")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If LastRow < 1 Or _
Target < "" Then

LastRow = LastRow + 1
End If

.Range("A" & LastRow) = Target.Value
If LastRow = 1 Then
.Range("B1") = "Changed"
Else
If .Range("A" & LastRow) = _
.Range("A" & (LastRow - 1)) Then

.Range("B" & LastRow) = "Not Changed"
Else
.Range("B" & LastRow) = "Changed"
End If
End If
End With
End If

End Sub


"Glenn" wrote:

I would like to know if this is possible? I would like to create a
spreadsheet that would have three calls. first cell C5 as a daily score cell,
then have another cell C10 only add all the daily score and count how many
times there was a change in the score, Then have cell C15 take the total of
cell C10 divide the amount of daily score and come up with a average. If I am
barking up the wrong software can you point me in the right direction.
--
Thank You
Glenn

I probably didn't supply enough information for a novice to get everything
working. People usually refere to worksheet functions starting with equal
signs as "formulas" and the SUBs in VBA as macros. Her is additional
information

1) Make sure your worksheet and a sheet named "Changes" (no double quote).
Right click on one of the tabs on the bottom of the worksheet like the one
called SHEET3
1) In C5 don't add anything except the numbers. the worksheet change will
take the numbers that you enter In C5 and automatically put them on the
worksheet called Changes.

2) In C10 enter =COUNTIF(Scores!$B:$B,"Changed")

3) In C15 =SUM(Scores!$A:$A)/C10

4) You need to add the macro to the VBA sheets. Type Alt-F11 from the
worksheet to get to VBA. Go to menu: View- Project Explorer. In the
explorer window look for the sheet name where you are placing the formulas
like Sheet1 or equivalent. Double click the sheet name you will see the
sheet name on the Top of the VBA window like [ Sheet1 (code)] to indicate
your are on the correct sheet. Copy the macro below from: Sub to: End Sub
and paste the code on the [ Sheet1 (code)] sheet. To check that the code is
ok go to VBA menu DEBUG - Compile Project. No errors should occur.


5) Now go back to C5 and start typing number. the number should
automatically get placed on the sheet "Changes.

6) If it is no working you need to change your security level to medium. Go
to Worksheet menu Tools - Macro - Security and Select Medium. Save your
workbook and exit. The re-enter the workbook a pop up menu wil ask you if
you want to enable macros. Select Yes. Go back and retry entering number in
C5 and see if they appear on the sheet Changes.


Sub worksheet_change(ByVal Target As Range)

If Target.Address = "$C$5" Then
With Sheets("Scores")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If LastRow < 1 Or _
Target < "" Then

LastRow = LastRow + 1
End If

.Range("A" & LastRow) = Target.Value
If LastRow = 1 Then
.Range("B1") = "Changed"
Else
If .Range("A" & LastRow) = _
.Range("A" & (LastRow - 1)) Then

.Range("B" & LastRow) = "Not Changed"
Else
.Range("B" & LastRow) = "Changed"
End If
End If
End With
End If

End Sub



"Glenn" wrote:

Joel,

I am a novice at Excel.

When I create Cell C5 I am going to create a Macro.
=COUNTIF(Scores!$B:$B,"Changed")
=COUNTIF(scores!$C:$5,"Changed") ?
I have tried it several ways and haven't had much success. Am I doing
something wrong? Any is deeply appreciated!
--
Thank You
Glenn


"Joel" wrote:

Using the worksheet change macro I put the scores on a worksheet called SCORES.
Column A is the scores and column B get either "Changed" or "Not Changed".
I assumed the 1st entry will always be a change since there was no previous
data.

When you enter a number in cell C5 the worksheet change function
automaticaly gets called.

the number of chaes in C10 is a formula
=COUNTIF(Scores!$B:$B,"Changed")

The results in C15 is
=SUM(Scores!$A:$A)/C10

Sub worksheet_change(ByVal Target As Range)

If Target.Address = "$C$5" Then
With Sheets("Scores")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If LastRow < 1 Or _
Target < "" Then

LastRow = LastRow + 1
End If

.Range("A" & LastRow) = Target.Value
If LastRow = 1 Then
.Range("B1") = "Changed"
Else
If .Range("A" & LastRow) = _
.Range("A" & (LastRow - 1)) Then

.Range("B" & LastRow) = "Not Changed"
Else
.Range("B" & LastRow) = "Changed"
End If
End If
End With
End If

End Sub


"Glenn" wrote:

I would like to know if this is possible? I would like to create a
spreadsheet that would have three calls. first cell C5 as a daily score cell,
then have another cell C10 only add all the daily score and count how many
times there was a change in the score, Then have cell C15 take the total of
cell C10 divide the amount of daily score and come up with a average. If I am
barking up the wrong software can you point me in the right direction.
--
Thank You
Glenn

Joel,

I thank you for all of your help. As I said I am a novice to Excel and I
will be working on this in the next few days. Is there any difference that I
am using Office 2003 with the formulas?
Also cells C5 and C15 will remain on the same sheet called "Scores", just
"Changed" will be on its own sheet?
Joel I am sorry if I am a little hard headed this is somewhat new to me. I
am giving this my all on my days off.

Thank You Once Again!
Glenn


"Joel" wrote:

I probably didn't supply enough information for a novice to get everything
working. People usually refere to worksheet functions starting with equal
signs as "formulas" and the SUBs in VBA as macros. Her is additional
information

1) Make sure your worksheet and a sheet named "Changes" (no double quote).
Right click on one of the tabs on the bottom of the worksheet like the one
called SHEET3
1) In C5 don't add anything except the numbers. the worksheet change will
take the numbers that you enter In C5 and automatically put them on the
worksheet called Changes.

2) In C10 enter =COUNTIF(Scores!$B:$B,"Changed")

3) In C15 =SUM(Scores!$A:$A)/C10

4) You need to add the macro to the VBA sheets. Type Alt-F11 from the
worksheet to get to VBA. Go to menu: View- Project Explorer. In the
explorer window look for the sheet name where you are placing the formulas
like Sheet1 or equivalent. Double click the sheet name you will see the
sheet name on the Top of the VBA window like [ Sheet1 (code)] to indicate
your are on the correct sheet. Copy the macro below from: Sub to: End Sub
and paste the code on the [ Sheet1 (code)] sheet. To check that the code is
ok go to VBA menu DEBUG - Compile Project. No errors should occur.


5) Now go back to C5 and start typing number. the number should
automatically get placed on the sheet "Changes.

6) If it is no working you need to change your security level to medium. Go
to Worksheet menu Tools - Macro - Security and Select Medium. Save your
workbook and exit. The re-enter the workbook a pop up menu wil ask you if
you want to enable macros. Select Yes. Go back and retry entering number in
C5 and see if they appear on the sheet Changes.


Sub worksheet_change(ByVal Target As Range)

If Target.Address = "$C$5" Then
With Sheets("Scores")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If LastRow < 1 Or _
Target < "" Then

LastRow = LastRow + 1
End If

.Range("A" & LastRow) = Target.Value
If LastRow = 1 Then
.Range("B1") = "Changed"
Else
If .Range("A" & LastRow) = _
.Range("A" & (LastRow - 1)) Then

.Range("B" & LastRow) = "Not Changed"
Else
.Range("B" & LastRow) = "Changed"
End If
End If
End With
End If

End Sub



"Glenn" wrote:

Joel,

I am a novice at Excel.

When I create Cell C5 I am going to create a Macro.
=COUNTIF(Scores!$B:$B,"Changed")
=COUNTIF(scores!$C:$5,"Changed") ?
I have tried it several ways and haven't had much success. Am I doing
something wrong? Any is deeply appreciated!
--
Thank You
Glenn


"Joel" wrote:

Using the worksheet change macro I put the scores on a worksheet called SCORES.
Column A is the scores and column B get either "Changed" or "Not Changed".
I assumed the 1st entry will always be a change since there was no previous
data.

When you enter a number in cell C5 the worksheet change function
automaticaly gets called.

the number of chaes in C10 is a formula
=COUNTIF(Scores!$B:$B,"Changed")

The results in C15 is
=SUM(Scores!$A:$A)/C10

Sub worksheet_change(ByVal Target As Range)

If Target.Address = "$C$5" Then
With Sheets("Scores")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If LastRow < 1 Or _
Target < "" Then

LastRow = LastRow + 1
End If

.Range("A" & LastRow) = Target.Value
If LastRow = 1 Then
.Range("B1") = "Changed"
Else
If .Range("A" & LastRow) = _
.Range("A" & (LastRow - 1)) Then

.Range("B" & LastRow) = "Not Changed"
Else
.Range("B" & LastRow) = "Changed"
End If
End If
End With
End If

End Sub


"Glenn" wrote:

I would like to know if this is possible? I would like to create a
spreadsheet that would have three calls. first cell C5 as a daily score cell,
then have another cell C10 only add all the daily score and count how many
times there was a change in the score, Then have cell C15 take the total of
cell C10 divide the amount of daily score and come up with a average. If I am
barking up the wrong software can you point me in the right direction.
--
Thank You
Glenn

The best way to learn and debug problems is th ostep through the code and set
break points.

I would recommend putting a break point on the line.

Sub worksheet_change(ByVal Target As Range)


Break points need to be set every time the workbook is opened but will
retain the break point until removed by pressing F9 again.

Click on line above with mouse and then press F9. the line should turn
color. Go back to the worksheet and change cell C5. the macro should run
and stop on the break point. You can then step through the code by Press F8.

Other options can be found in the VBA menue Debug, and Run. You may need to
perform a Reset (Run - reset) to be able to start the code from the beginning
if you are having problems.

The hardest part for a worksheet Change Macro is getting it to run when the
cell is changed. The main problem is that the code is not placed on the
correct VBA sheets (must be the same VBA sheet as the sheet that is being
changed). Each worksheet has its own corresponding VBA sheet. See my
previous posting refering to the Project Manager.

You can see the values of any variable by hovering the mouse over the
variable. also you can add WATCH items by highlighting a variable like
LastRow and right Click Mouse. Then select ADD WATCH and press OK on the
pop-up window.

"Glenn" wrote:

Joel,

I thank you for all of your help. As I said I am a novice to Excel and I
will be working on this in the next few days. Is there any difference that I
am using Office 2003 with the formulas?
Also cells C5 and C15 will remain on the same sheet called "Scores", just
"Changed" will be on its own sheet?
Joel I am sorry if I am a little hard headed this is somewhat new to me. I
am giving this my all on my days off.

Thank You Once Again!
Glenn


"Joel" wrote:

I probably didn't supply enough information for a novice to get everything
working. People usually refere to worksheet functions starting with equal
signs as "formulas" and the SUBs in VBA as macros. Her is additional
information

1) Make sure your worksheet and a sheet named "Changes" (no double quote).
Right click on one of the tabs on the bottom of the worksheet like the one
called SHEET3
1) In C5 don't add anything except the numbers. the worksheet change will
take the numbers that you enter In C5 and automatically put them on the
worksheet called Changes.

2) In C10 enter =COUNTIF(Scores!$B:$B,"Changed")

3) In C15 =SUM(Scores!$A:$A)/C10

4) You need to add the macro to the VBA sheets. Type Alt-F11 from the
worksheet to get to VBA. Go to menu: View- Project Explorer. In the
explorer window look for the sheet name where you are placing the formulas
like Sheet1 or equivalent. Double click the sheet name you will see the
sheet name on the Top of the VBA window like [ Sheet1 (code)] to indicate
your are on the correct sheet. Copy the macro below from: Sub to: End Sub
and paste the code on the [ Sheet1 (code)] sheet. To check that the code is
ok go to VBA menu DEBUG - Compile Project. No errors should occur.


5) Now go back to C5 and start typing number. the number should
automatically get placed on the sheet "Changes.

6) If it is no working you need to change your security level to medium. Go
to Worksheet menu Tools - Macro - Security and Select Medium. Save your
workbook and exit. The re-enter the workbook a pop up menu wil ask you if
you want to enable macros. Select Yes. Go back and retry entering number in
C5 and see if they appear on the sheet Changes.


Sub worksheet_change(ByVal Target As Range)

If Target.Address = "$C$5" Then
With Sheets("Scores")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If LastRow < 1 Or _
Target < "" Then

LastRow = LastRow + 1
End If

.Range("A" & LastRow) = Target.Value
If LastRow = 1 Then
.Range("B1") = "Changed"
Else
If .Range("A" & LastRow) = _
.Range("A" & (LastRow - 1)) Then

.Range("B" & LastRow) = "Not Changed"
Else
.Range("B" & LastRow) = "Changed"
End If
End If
End With
End If

End Sub



"Glenn" wrote:

Joel,

I am a novice at Excel.

When I create Cell C5 I am going to create a Macro.
=COUNTIF(Scores!$B:$B,"Changed")
=COUNTIF(scores!$C:$5,"Changed") ?
I have tried it several ways and haven't had much success. Am I doing
something wrong? Any is deeply appreciated!
--
Thank You
Glenn


"Joel" wrote:

Using the worksheet change macro I put the scores on a worksheet called SCORES.
Column A is the scores and column B get either "Changed" or "Not Changed".
I assumed the 1st entry will always be a change since there was no previous
data.

When you enter a number in cell C5 the worksheet change function
automaticaly gets called.

the number of chaes in C10 is a formula
=COUNTIF(Scores!$B:$B,"Changed")

The results in C15 is
=SUM(Scores!$A:$A)/C10

Sub worksheet_change(ByVal Target As Range)

If Target.Address = "$C$5" Then
With Sheets("Scores")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If LastRow < 1 Or _
Target < "" Then

LastRow = LastRow + 1
End If

.Range("A" & LastRow) = Target.Value
If LastRow = 1 Then
.Range("B1") = "Changed"
Else
If .Range("A" & LastRow) = _
.Range("A" & (LastRow - 1)) Then

.Range("B" & LastRow) = "Not Changed"
Else
.Range("B" & LastRow) = "Changed"
End If
End If
End With
End If

End Sub


"Glenn" wrote:

I would like to know if this is possible? I would like to create a
spreadsheet that would have three calls. first cell C5 as a daily score cell,
then have another cell C10 only add all the daily score and count how many
times there was a change in the score, Then have cell C15 take the total of
cell C10 divide the amount of daily score and come up with a average. If I am
barking up the wrong software can you point me in the right direction.
--
Thank You
Glenn