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

Hello Joel,

First of all, Thank you again for your time and help. I took an Excel class
and bought some books to help me along with the process of learning and
becoming more proficient in Excel.
I have tried several different ways to to get my ideas to work. I was told
from an my Excel teacher that excel may not be the program that I would use
to accomplish my goal.
I have typed out my project so you may get a better understanding of what I
am looking to have this do. My trouble areas is having the the daily input
cells being count for each time they are changed and also having the daily
input cells being tallied for total of all the number inputed.

My problem for not doing a long daily sheet is we have over 300 dealers to
evaluate in one day.


Table Games Ratings

What I would like the Program to look like and do?

Next to the associates ID there would be a row of the games that we offer.
Under the games we offer there would be three boxes for each game colorized.
In these boxes Floorperson would place the number between 1 €œExcellent €œto 6
€œUnsatisfactory€� for each of the three boxes under the desired game.

1. Game knowledge
2. Game pace
3. Customer Skills

Once the floorperson puts in the rating for the day and saves it. The chain
of events will happen as seen below. The information collect for the
particular game in each cell will be counted in a separate cell or screen
which adds only the times that cell was changed. Then the next screen a
separate set of cells €œTally€� will continually compute the total amount of
daily ratings inputed for that specific game.

Once that data has been collected in the tally cells it would then be
divided by the number in the counter cell to achieve the dealers average for
a specific game. The only difference will be in the final average, Game
knowledge and Game Pace will be added together to become one rating.


The bottom would have a submit button to trigger all of the ratings to
complete.


Page 1

SAMPLE: Employee ID 1234

Daily Ratings. €œTransaction screen for daily ratings€� Changes daily.
Name ID # BJ CR RO BA
John Doe 1234
Game Knowledge 1 Game Pace 2
Customer Skills 1

Page 2

Unseen
Cell Counter €œHow many times the cell has been changed€�
Name ID #
John Doe 1234 BJ CR RO BA
Game Knowledge 5 Game Pace 5
Customer Skills 5

Unseen
Tally Screen. €œTotal amount of ratings imputed per game€�
Name ID #
John Doe 1234 BJ CR RO BA
Game Knowledge 6
Game Pace 7
Customer Skills 6

Page 3

Visible to specific positions.
€œAverages€� this screen will merge game knowledge and game pace together to
give two averages game rating and customer skills.
Name ID # BJ CR RO BA
John Doe 1234
Game Rating 1
Customer Skills 1

Joel does this make sense to you? Do you think that I can get this to work?

Thank You Once Again For Your Input!!
Glenn


"Joel" wrote:

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

I have programmed in many languges. VBA in Excel has two advantages from
other languages.

1) It is easy to see a dabase entered into excel
2) It is easy to see the data when you are debugging the code.

I have read through your ideas. I think Excel is a godd easy language to
implemnt you code. I have a few questions


1) Do you plan to use UserForms for the inputs? the submit button can be on
a userform. If multiple userforms are used then a next and back button may
be useful.
2) when you refer to cells are you refering to GAME Cells or Excel cells?
How do the Gam e Cells map to the Excel Cellls.
3) I don't know hnow many cells you need to keep track of changes. This can
be implimented using Worksheet Change function(s). each worksheet requires
its own change function. One issue is keeping track of real changes and not
typo errors. It nice if you can make all the changes from Listboxes,
comboboxes or validation lists so the user can't type wrong values. You may
want to keep a historyy of all previous values on a worksheet and then count
the number of changes or just count the changes.

Probably the best way is to store the changes in a seperate worksheet.


"Glenn" wrote:

Hello Joel,

First of all, Thank you again for your time and help. I took an Excel class
and bought some books to help me along with the process of learning and
becoming more proficient in Excel.
I have tried several different ways to to get my ideas to work. I was told
from an my Excel teacher that excel may not be the program that I would use
to accomplish my goal.
I have typed out my project so you may get a better understanding of what I
am looking to have this do. My trouble areas is having the the daily input
cells being count for each time they are changed and also having the daily
input cells being tallied for total of all the number inputed.

My problem for not doing a long daily sheet is we have over 300 dealers to
evaluate in one day.


Table Games Ratings

What I would like the Program to look like and do?

Next to the associates ID there would be a row of the games that we offer.
Under the games we offer there would be three boxes for each game colorized.
In these boxes Floorperson would place the number between 1 €œExcellent €œto 6
€œUnsatisfactory€� for each of the three boxes under the desired game.

1. Game knowledge
2. Game pace
3. Customer Skills

Once the floorperson puts in the rating for the day and saves it. The chain
of events will happen as seen below. The information collect for the
particular game in each cell will be counted in a separate cell or screen
which adds only the times that cell was changed. Then the next screen a
separate set of cells €œTally€� will continually compute the total amount of
daily ratings inputed for that specific game.

Once that data has been collected in the tally cells it would then be
divided by the number in the counter cell to achieve the dealers average for
a specific game. The only difference will be in the final average, Game
knowledge and Game Pace will be added together to become one rating.


The bottom would have a submit button to trigger all of the ratings to
complete.


Page 1

SAMPLE: Employee ID 1234

Daily Ratings. €œTransaction screen for daily ratings€� Changes daily.
Name ID # BJ CR RO BA
John Doe 1234
Game Knowledge 1 Game Pace 2
Customer Skills 1

Page 2

Unseen
Cell Counter €œHow many times the cell has been changed€�
Name ID #
John Doe 1234 BJ CR RO BA
Game Knowledge 5 Game Pace 5
Customer Skills 5

Unseen
Tally Screen. €œTotal amount of ratings imputed per game€�
Name ID #
John Doe 1234 BJ CR RO BA
Game Knowledge 6
Game Pace 7
Customer Skills 6

Page 3

Visible to specific positions.
€œAverages€� this screen will merge game knowledge and game pace together to
give two averages game rating and customer skills.
Name ID # BJ CR RO BA
John Doe 1234
Game Rating 1
Customer Skills 1

Joel does this make sense to you? Do you think that I can get this to work?

Thank You Once Again For Your Input!!
Glenn


"Joel" wrote:

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

Joel,

I would like to use VBA to create this program. First let tell you, you
are the only person that I've talked to that has any idea of what I am trying
to do. I will answer the questions in the oder you asked me.

1) I would like to use Userforms for the inputs. Also I would like to have
next and back buttons. They would come in handy. I am going to get a book on
VBA tonight.
2) Yes when I refer to the cells Game Knowledge that is one of the cells in
my Excel worksheet. Let me try to explain myself better. There would be a
header above with the name of the game we are rating for that day. Under the
header there would be three boxes that we would input the ratings. To the
left of the first empty cell would a header with "Game Knowledge". Which is
were we would place the rating for the day. Beneath that would be the two
other areas of ratings.
3) There will be three cells that would need to be tracked. 1 Game knowledge
2 Game pace and 3 Customer Skills. I think list boxes or Combo boxes would
defiantly help protect against errors.

The problem that I am encountering is counting each time you change the 3
cells in Excel Game knowledge, Game pace and Customer skills. Also having
each time you enter new numbers in the cells they are tallied. As far as the
averaging that I could handle.

I have made 4 different sheets inside one workbook. One sheet is named
"Rating" for daily inputs, second sheet "Changed" is the one I am trying to
have as a counter for the changes in the 3 cells, Game knowledge Game pace
and Cust skills. The third cell is the "Tally" Sheet. this sheet I am using
to continually add the numbers from all 3 cells Game knowledge Game pace &
Customer skills from the Ratings sheet.

My main problem is counting the changes in each of the cells and the tally
of the ratings cells changed.
-- is there a way for me to email you the sheet I have made?

Thank You
Glenn


"Joel" wrote:

I have programmed in many languges. VBA in Excel has two advantages from
other languages.

1) It is easy to see a dabase entered into excel
2) It is easy to see the data when you are debugging the code.

I have read through your ideas. I think Excel is a godd easy language to
implemnt you code. I have a few questions


1) Do you plan to use UserForms for the inputs? the submit button can be on
a userform. If multiple userforms are used then a next and back button may
be useful.
2) when you refer to cells are you refering to GAME Cells or Excel cells?
How do the Gam e Cells map to the Excel Cellls.
3) I don't know hnow many cells you need to keep track of changes. This can
be implimented using Worksheet Change function(s). each worksheet requires
its own change function. One issue is keeping track of real changes and not
typo errors. It nice if you can make all the changes from Listboxes,
comboboxes or validation lists so the user can't type wrong values. You may
want to keep a historyy of all previous values on a worksheet and then count
the number of changes or just count the changes.

Probably the best way is to store the changes in a seperate worksheet.


"Glenn" wrote:

Hello Joel,

First of all, Thank you again for your time and help. I took an Excel class
and bought some books to help me along with the process of learning and
becoming more proficient in Excel.
I have tried several different ways to to get my ideas to work. I was told
from an my Excel teacher that excel may not be the program that I would use
to accomplish my goal.
I have typed out my project so you may get a better understanding of what I
am looking to have this do. My trouble areas is having the the daily input
cells being count for each time they are changed and also having the daily
input cells being tallied for total of all the number inputed.

My problem for not doing a long daily sheet is we have over 300 dealers to
evaluate in one day.


Table Games Ratings

What I would like the Program to look like and do?

Next to the associates ID there would be a row of the games that we offer.
Under the games we offer there would be three boxes for each game colorized.
In these boxes Floorperson would place the number between 1 €œExcellent €œto 6
€œUnsatisfactory€� for each of the three boxes under the desired game.

1. Game knowledge
2. Game pace
3. Customer Skills

Once the floorperson puts in the rating for the day and saves it. The chain
of events will happen as seen below. The information collect for the
particular game in each cell will be counted in a separate cell or screen
which adds only the times that cell was changed. Then the next screen a
separate set of cells €œTally€� will continually compute the total amount of
daily ratings inputed for that specific game.

Once that data has been collected in the tally cells it would then be
divided by the number in the counter cell to achieve the dealers average for
a specific game. The only difference will be in the final average, Game
knowledge and Game Pace will be added together to become one rating.


The bottom would have a submit button to trigger all of the ratings to
complete.


Page 1

SAMPLE: Employee ID 1234

Daily Ratings. €œTransaction screen for daily ratings€� Changes daily.
Name ID # BJ CR RO BA
John Doe 1234
Game Knowledge 1 Game Pace 2
Customer Skills 1

Page 2

Unseen
Cell Counter €œHow many times the cell has been changed€�
Name ID #
John Doe 1234 BJ CR RO BA
Game Knowledge 5 Game Pace 5
Customer Skills 5

Unseen
Tally Screen. €œTotal amount of ratings imputed per game€�
Name ID #
John Doe 1234 BJ CR RO BA
Game Knowledge 6
Game Pace 7
Customer Skills 6

Page 3

Visible to specific positions.
€œAverages€� this screen will merge game knowledge and game pace together to
give two averages game rating and customer skills.
Name ID # BJ CR RO BA
John Doe 1234
Game Rating 1
Customer Skills 1

Joel does this make sense to you? Do you think that I can get this to work?

Thank You Once Again For Your Input!!
Glenn


"Joel" wrote:

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

Yes you can email me. I will also try to setup the userform for you

joel dot warburg at itt dot com

"Glenn" wrote:

Joel,

I would like to use VBA to create this program. First let tell you, you
are the only person that I've talked to that has any idea of what I am trying
to do. I will answer the questions in the oder you asked me.

1) I would like to use Userforms for the inputs. Also I would like to have
next and back buttons. They would come in handy. I am going to get a book on
VBA tonight.
2) Yes when I refer to the cells Game Knowledge that is one of the cells in
my Excel worksheet. Let me try to explain myself better. There would be a
header above with the name of the game we are rating for that day. Under the
header there would be three boxes that we would input the ratings. To the
left of the first empty cell would a header with "Game Knowledge". Which is
were we would place the rating for the day. Beneath that would be the two
other areas of ratings.
3) There will be three cells that would need to be tracked. 1 Game knowledge
2 Game pace and 3 Customer Skills. I think list boxes or Combo boxes would
defiantly help protect against errors.

The problem that I am encountering is counting each time you change the 3
cells in Excel Game knowledge, Game pace and Customer skills. Also having
each time you enter new numbers in the cells they are tallied. As far as the
averaging that I could handle.

I have made 4 different sheets inside one workbook. One sheet is named
"Rating" for daily inputs, second sheet "Changed" is the one I am trying to
have as a counter for the changes in the 3 cells, Game knowledge Game pace
and Cust skills. The third cell is the "Tally" Sheet. this sheet I am using
to continually add the numbers from all 3 cells Game knowledge Game pace &
Customer skills from the Ratings sheet.

My main problem is counting the changes in each of the cells and the tally
of the ratings cells changed.
-- is there a way for me to email you the sheet I have made?

Thank You
Glenn


"Joel" wrote:

I have programmed in many languges. VBA in Excel has two advantages from
other languages.

1) It is easy to see a dabase entered into excel
2) It is easy to see the data when you are debugging the code.

I have read through your ideas. I think Excel is a godd easy language to
implemnt you code. I have a few questions


1) Do you plan to use UserForms for the inputs? the submit button can be on
a userform. If multiple userforms are used then a next and back button may
be useful.
2) when you refer to cells are you refering to GAME Cells or Excel cells?
How do the Gam e Cells map to the Excel Cellls.
3) I don't know hnow many cells you need to keep track of changes. This can
be implimented using Worksheet Change function(s). each worksheet requires
its own change function. One issue is keeping track of real changes and not
typo errors. It nice if you can make all the changes from Listboxes,
comboboxes or validation lists so the user can't type wrong values. You may
want to keep a historyy of all previous values on a worksheet and then count
the number of changes or just count the changes.

Probably the best way is to store the changes in a seperate worksheet.


"Glenn" wrote:

Hello Joel,

First of all, Thank you again for your time and help. I took an Excel class
and bought some books to help me along with the process of learning and
becoming more proficient in Excel.
I have tried several different ways to to get my ideas to work. I was told
from an my Excel teacher that excel may not be the program that I would use
to accomplish my goal.
I have typed out my project so you may get a better understanding of what I
am looking to have this do. My trouble areas is having the the daily input
cells being count for each time they are changed and also having the daily
input cells being tallied for total of all the number inputed.

My problem for not doing a long daily sheet is we have over 300 dealers to
evaluate in one day.


Table Games Ratings

What I would like the Program to look like and do?

Next to the associates ID there would be a row of the games that we offer.
Under the games we offer there would be three boxes for each game colorized.
In these boxes Floorperson would place the number between 1 €œExcellent €œto 6
€œUnsatisfactory€� for each of the three boxes under the desired game.

1. Game knowledge
2. Game pace
3. Customer Skills

Once the floorperson puts in the rating for the day and saves it. The chain
of events will happen as seen below. The information collect for the
particular game in each cell will be counted in a separate cell or screen
which adds only the times that cell was changed. Then the next screen a
separate set of cells €œTally€� will continually compute the total amount of
daily ratings inputed for that specific game.

Once that data has been collected in the tally cells it would then be
divided by the number in the counter cell to achieve the dealers average for
a specific game. The only difference will be in the final average, Game
knowledge and Game Pace will be added together to become one rating.


The bottom would have a submit button to trigger all of the ratings to
complete.


Page 1

SAMPLE: Employee ID 1234

Daily Ratings. €œTransaction screen for daily ratings€� Changes daily.
Name ID # BJ CR RO BA
John Doe 1234
Game Knowledge 1 Game Pace 2
Customer Skills 1

Page 2

Unseen
Cell Counter €œHow many times the cell has been changed€�
Name ID #
John Doe 1234 BJ CR RO BA
Game Knowledge 5 Game Pace 5
Customer Skills 5

Unseen
Tally Screen. €œTotal amount of ratings imputed per game€�
Name ID #
John Doe 1234 BJ CR RO BA
Game Knowledge 6
Game Pace 7
Customer Skills 6

Page 3

Visible to specific positions.
€œAverages€� this screen will merge game knowledge and game pace together to
give two averages game rating and customer skills.
Name ID # BJ CR RO BA
John Doe 1234
Game Rating 1
Customer Skills 1

Joel does this make sense to you? Do you think that I can get this to work?

Thank You Once Again For Your Input!!
Glenn


"Joel" wrote:

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