Adding cells, using text as number
 

HELP! I haven't worked in Excel much, but I know there's an equation I can
use to get the information I'm wanting....
For example, I'm trying to keep tract of how many apples everyone eats. I'm
going to assign the letter "a" to Amy, "b" to Betty, "c" to Cathy, etc. Amy
eats an apple, so in cell A1, I'm going to type "a", and I want a "1" to go
to cell B6. (=COUNTIF(A1,"a")
Now here's where I'm getting myself confused....now Cathy's eaten an apple,
so I want to type a "c", and I want a "1" to go to cell B8.
(=COUNTIF(A1,"c"). But when I type the "c", it takes my "1" away from Amy.
Do I need to add something to my formula?!
Also, I need to start my count at "0". How can I do this, and keep tract of
my TOTAL amount of apples eaten?!

You can use a worksheet_change event. Right-click on the
worksheet tab, select "View Code", and paste the code
below in the window. Close VBE and save the workbook:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim aCtr As Range
Dim cCtr As Range
Set aCtr = Range("B6")
Set cCtr = Range("B8")
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Target
If .Value = "a" Then
aCtr.Value = aCtr.Value + 1
ElseIf .Value = "c" Then
cCtr.Value = cCtr.Value + 1
End If
End With
End If
End Sub

-----Original Message-----
HELP! I haven't worked in Excel much, but I know there's
an equation I can
use to get the information I'm wanting....
For example, I'm trying to keep tract of how many apples
everyone eats. I'm
going to assign the letter "a" to Amy, "b" to Betty, "c"
to Cathy, etc. Amy
eats an apple, so in cell A1, I'm going to type "a", and
I want a "1" to go
to cell B6. (=COUNTIF(A1,"a")
Now here's where I'm getting myself confused....now
Cathy's eaten an apple,
so I want to type a "c", and I want a "1" to go to cell
B8.
(=COUNTIF(A1,"c"). But when I type the "c", it takes
my "1" away from Amy.
Do I need to add something to my formula?!
Also, I need to start my count at "0". How can I do
this, and keep tract of
my TOTAL amount of apples eaten?!
.

THANK YOU!!! THANK YOU!!! THANK YOU!!!
You're infomation has been wonderfully helpful AND informative!!!

(If you get a minute, could you explain something to me?! At the end of
your code, you've got a lot of end if, end with, end if, end sub. What does
all that mean?!)

"Jason Morin" wrote:

You can use a worksheet_change event. Right-click on the
worksheet tab, select "View Code", and paste the code
below in the window. Close VBE and save the workbook:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim aCtr As Range
Dim cCtr As Range
Set aCtr = Range("B6")
Set cCtr = Range("B8")
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Target
If .Value = "a" Then
aCtr.Value = aCtr.Value + 1
ElseIf .Value = "c" Then
cCtr.Value = cCtr.Value + 1
End If
End With
End If
End Sub

-----Original Message-----
HELP! I haven't worked in Excel much, but I know there's
an equation I can
use to get the information I'm wanting....
For example, I'm trying to keep tract of how many apples
everyone eats. I'm
going to assign the letter "a" to Amy, "b" to Betty, "c"
to Cathy, etc. Amy
eats an apple, so in cell A1, I'm going to type "a", and
I want a "1" to go
to cell B6. (=COUNTIF(A1,"a")
Now here's where I'm getting myself confused....now
Cathy's eaten an apple,
so I want to type a "c", and I want a "1" to go to cell
B8.
(=COUNTIF(A1,"c"). But when I type the "c", it takes
my "1" away from Amy.
Do I need to add something to my formula?!
Also, I need to start my count at "0". How can I do
this, and keep tract of
my TOTAL amount of apples eaten?!
.

Each With statement needs a corresponding End With statement. Ditto for
multi-line If statements. The reason is that you can have many statements that
are executed if the test is true, and multiple statements for the false part
as well.

On Mon, 10 Jan 2005 06:45:02 -0800, Pgsmom
wrote:

THANK YOU!!! THANK YOU!!! THANK YOU!!!
You're infomation has been wonderfully helpful AND informative!!!

(If you get a minute, could you explain something to me?! At the end of
your code, you've got a lot of end if, end with, end if, end sub. What does
all that mean?!)

"Jason Morin" wrote:

You can use a worksheet_change event. Right-click on the
worksheet tab, select "View Code", and paste the code
below in the window. Close VBE and save the workbook:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim aCtr As Range
Dim cCtr As Range
Set aCtr = Range("B6")
Set cCtr = Range("B8")
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Target
If .Value = "a" Then
aCtr.Value = aCtr.Value + 1
ElseIf .Value = "c" Then
cCtr.Value = cCtr.Value + 1
End If
End With
End If
End Sub

-----Original Message-----
HELP! I haven't worked in Excel much, but I know there's
an equation I can
use to get the information I'm wanting....
For example, I'm trying to keep tract of how many apples
everyone eats. I'm
going to assign the letter "a" to Amy, "b" to Betty, "c"
to Cathy, etc. Amy
eats an apple, so in cell A1, I'm going to type "a", and
I want a "1" to go
to cell B6. (=COUNTIF(A1,"a")
Now here's where I'm getting myself confused....now
Cathy's eaten an apple,
so I want to type a "c", and I want a "1" to go to cell
B8.
(=COUNTIF(A1,"c"). But when I type the "c", it takes
my "1" away from Amy.
Do I need to add something to my formula?!
Also, I need to start my count at "0". How can I do
this, and keep tract of
my TOTAL amount of apples eaten?!
.

Thank you again!!! You have been very informative!!!

"Myrna Larson" wrote:

Each With statement needs a corresponding End With statement. Ditto for
multi-line If statements. The reason is that you can have many statements that
are executed if the test is true, and multiple statements for the false part
as well.

On Mon, 10 Jan 2005 06:45:02 -0800, Pgsmom
wrote:

THANK YOU!!! THANK YOU!!! THANK YOU!!!
You're infomation has been wonderfully helpful AND informative!!!

(If you get a minute, could you explain something to me?! At the end of
your code, you've got a lot of end if, end with, end if, end sub. What does
all that mean?!)

"Jason Morin" wrote:

You can use a worksheet_change event. Right-click on the
worksheet tab, select "View Code", and paste the code
below in the window. Close VBE and save the workbook:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim aCtr As Range
Dim cCtr As Range
Set aCtr = Range("B6")
Set cCtr = Range("B8")
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Target
If .Value = "a" Then
aCtr.Value = aCtr.Value + 1
ElseIf .Value = "c" Then
cCtr.Value = cCtr.Value + 1
End If
End With
End If
End Sub

-----Original Message-----
HELP! I haven't worked in Excel much, but I know there's
an equation I can
use to get the information I'm wanting....
For example, I'm trying to keep tract of how many apples
everyone eats. I'm
going to assign the letter "a" to Amy, "b" to Betty, "c"
to Cathy, etc. Amy
eats an apple, so in cell A1, I'm going to type "a", and
I want a "1" to go
to cell B6. (=COUNTIF(A1,"a")
Now here's where I'm getting myself confused....now
Cathy's eaten an apple,
so I want to type a "c", and I want a "1" to go to cell
B8.
(=COUNTIF(A1,"c"). But when I type the "c", it takes
my "1" away from Amy.
Do I need to add something to my formula?!
Also, I need to start my count at "0". How can I do
this, and keep tract of
my TOTAL amount of apples eaten?!
.