I have 3 worksheets,wanted to find the total sum of the values appeared in a
column (F say) in each of the worksheets. My following codes do not give me
the right answer. Instead it sums only the first sheet, but output triple the
total value of the FIRST sheet and ignores the other 2 sheets. Can anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total

You may need an activate:

Sub Macro1()
Dim w As Worksheet
t = 0
For Each w In Worksheets
w.Activate
t = t + Cells(1, 1)
Next
MsgBox (t)
End Sub

will work. There may be other problems as well

--
Gary's Student


"zhj23" wrote:

I have 3 worksheets,wanted to find the total sum of the values appeared in a
column (F say) in each of the worksheets. My following codes do not give me
the right answer. Instead it sums only the first sheet, but output triple the
total value of the FIRST sheet and ignores the other 2 sheets. Can anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total

Hello! Friend

Thanks for writing.

But it didn't work. Still gives triple the sum of Sheet 1 only.


zhj23

"Gary''s Student" wrote:

You may need an activate:

Sub Macro1()
Dim w As Worksheet
t = 0
For Each w In Worksheets
w.Activate
t = t + Cells(1, 1)
Next
MsgBox (t)
End Sub

will work. There may be other problems as well

--
Gary's Student


"zhj23" wrote:

I have 3 worksheets,wanted to find the total sum of the values appeared in a
column (F say) in each of the worksheets. My following codes do not give me
the right answer. Instead it sums only the first sheet, but output triple the
total value of the FIRST sheet and ignores the other 2 sheets. Can anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total

How about the range? I noticed it has a . rather than a :
--
Gary''s Student


"zhj23" wrote:

Hello! Friend

Thanks for writing.

But it didn't work. Still gives triple the sum of Sheet 1 only.


zhj23

"Gary''s Student" wrote:

You may need an activate:

Sub Macro1()
Dim w As Worksheet
t = 0
For Each w In Worksheets
w.Activate
t = t + Cells(1, 1)
Next
MsgBox (t)
End Sub

will work. There may be other problems as well

--
Gary's Student


"zhj23" wrote:

I have 3 worksheets,wanted to find the total sum of the values appeared in a
column (F say) in each of the worksheets. My following codes do not give me
the right answer. Instead it sums only the first sheet, but output triple the
total value of the FIRST sheet and ignores the other 2 sheets. Can anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total

see if this will do what you want

Sub addSheets2()
Dim shtotal As Integer
Dim total As Integer
Dim wkSht As Worksheet
Dim rng As Range

Dim i As Integer
total = 0
For Each wkSht In ThisWorkbook.Worksheets
Set rng = wkSht.Range("a8:a50")
shtotal = Application.Sum(rng)
total = shtotal + total
shtotal = 0

Next wkSht
MsgBox total

End Sub

--


Gary


"zhj23" wrote in message
...
I have 3 worksheets,wanted to find the total sum of the values appeared in
a
column (F say) in each of the worksheets. My following codes do not give
me
the right answer. Instead it sums only the first sheet, but output triple
the
total value of the FIRST sheet and ignores the other 2 sheets. Can anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total

you can delete the dim i line, it was left over

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
see if this will do what you want

Sub addSheets2()
Dim shtotal As Integer
Dim total As Integer
Dim wkSht As Worksheet
Dim rng As Range

Dim i As Integer
total = 0
For Each wkSht In ThisWorkbook.Worksheets
Set rng = wkSht.Range("a8:a50")
shtotal = Application.Sum(rng)
total = shtotal + total
shtotal = 0

Next wkSht
MsgBox total

End Sub

--


Gary


"zhj23" wrote in message
...
I have 3 worksheets,wanted to find the total sum of the values appeared in
a
column (F say) in each of the worksheets. My following codes do not give
me
the right answer. Instead it sums only the first sheet, but output triple
the
total value of the FIRST sheet and ignores the other 2 sheets. Can anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total

i adapted your code instead of using mine, in case you're interested

Sub addsheets()
Dim wkSht As Worksheet
Dim rng As Range
total = 0
For Each wkSht In Worksheets
Set rng = wkSht.Range("a8:a50")
total = total + Application.Sum(rng)
Next wkSht
MsgBox ("Total = ") & total
End Sub

--


Gary


"zhj23" wrote in message
...
I have 3 worksheets,wanted to find the total sum of the values appeared in
a
column (F say) in each of the worksheets. My following codes do not give
me
the right answer. Instead it sums only the first sheet, but output triple
the
total value of the FIRST sheet and ignores the other 2 sheets. Can anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total

Hi! Gary


Yes. I am very interested to know what is wrong with my codes. So when you
adopt my codes, did it work?

I tried, but it doesn't work. Still the triple of the values in Sheet 1.

Thanks anyway.


zhj23

"Gary Keramidas" wrote:

i adapted your code instead of using mine, in case you're interested

Sub addsheets()
Dim wkSht As Worksheet
Dim rng As Range
total = 0
For Each wkSht In Worksheets
Set rng = wkSht.Range("a8:a50")
total = total + Application.Sum(rng)
Next wkSht
MsgBox ("Total = ") & total
End Sub

--


Gary


"zhj23" wrote in message
...
I have 3 worksheets,wanted to find the total sum of the values appeared in
a
column (F say) in each of the worksheets. My following codes do not give
me
the right answer. Instead it sums only the first sheet, but output triple
the
total value of the FIRST sheet and ignores the other 2 sheets. Can anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total

Sub addsheets4()

Dim total As Integer ' <<<<<<<<<<<<<<<<<<<<<added this
Dim rng As Range ' <<<<<<<<<<<<<<<<<<<<<<added this
Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets
Set rng = wkSht.Range("a8:a50") ' <<<<<<<< changed this
total = total + Application.Sum(rng) ' <<<<<changed this

Next wkSht

MsgBox ("Total = ") & total

End Sub


--


Gary


"zhj23" wrote in message
...
Hi! Gary


Yes. I am very interested to know what is wrong with my codes. So when you
adopt my codes, did it work?

I tried, but it doesn't work. Still the triple of the values in Sheet 1.

Thanks anyway.


zhj23

"Gary Keramidas" wrote:

i adapted your code instead of using mine, in case you're interested

Sub addsheets()
Dim wkSht As Worksheet
Dim rng As Range
total = 0
For Each wkSht In Worksheets
Set rng = wkSht.Range("a8:a50")
total = total + Application.Sum(rng)
Next wkSht
MsgBox ("Total = ") & total
End Sub

--


Gary


"zhj23" wrote in message
...
I have 3 worksheets,wanted to find the total sum of the values appeared
in
a
column (F say) in each of the worksheets. My following codes do not
give
me
the right answer. Instead it sums only the first sheet, but output
triple
the
total value of the FIRST sheet and ignores the other 2 sheets. Can
anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total

Gary

Sorry. I have done EXACTLY what you have suggested. It still doesnt work.

(Although the total is not necessary INTEGER, it does not bother me for
testing purposes)

FYI, I had made it work by using the FOR.. NEXT statement, but I want to
know why can't work with FOR EACH.

Zhj23


"Gary Keramidas" wrote:

Sub addsheets4()

Dim total As Integer ' <<<<<<<<<<<<<<<<<<<<<added this
Dim rng As Range ' <<<<<<<<<<<<<<<<<<<<<<added this
Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets
Set rng = wkSht.Range("a8:a50") ' <<<<<<<< changed this
total = total + Application.Sum(rng) ' <<<<<changed this

Next wkSht

MsgBox ("Total = ") & total

End Sub


--


Gary


"zhj23" wrote in message
...
Hi! Gary


Yes. I am very interested to know what is wrong with my codes. So when you
adopt my codes, did it work?

I tried, but it doesn't work. Still the triple of the values in Sheet 1.

Thanks anyway.


zhj23

"Gary Keramidas" wrote:

i adapted your code instead of using mine, in case you're interested

Sub addsheets()
Dim wkSht As Worksheet
Dim rng As Range
total = 0
For Each wkSht In Worksheets
Set rng = wkSht.Range("a8:a50")
total = total + Application.Sum(rng)
Next wkSht
MsgBox ("Total = ") & total
End Sub

--


Gary


"zhj23" wrote in message
...
I have 3 worksheets,wanted to find the total sum of the values appeared
in
a
column (F say) in each of the worksheets. My following codes do not
give
me
the right answer. Instead it sums only the first sheet, but output
triple
the
total value of the FIRST sheet and ignores the other 2 sheets. Can
anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total

just created a new workbook and pasted your code in
added the 2 dim statements, total and rng, and the 2 lines between the for
and next of your code

i have a total of in column a of 260 on sheet1, 70 on sheet2 and 195 in
sheet3. this totals 525 and that's what i get,.

are you sure you set this line, Set rng = wkSht.Range("a8:a50"), in the for
next loop?

are you saying that if you copy and paste this code and run it it gives you
3* the column A total of sheet1?

Sub addsheets()
Dim wkSht As Worksheet
Dim rng As Range
total = 0
For Each wkSht In Worksheets
Set rng = wkSht.Range("a8:a50")
total = total + Application.Sum(rng)
Next wkSht
MsgBox ("Total = ") & total
End Sub

--


Gary


"zhj23" wrote in message
...
Gary

Sorry. I have done EXACTLY what you have suggested. It still doesnt work.

(Although the total is not necessary INTEGER, it does not bother me for
testing purposes)

FYI, I had made it work by using the FOR.. NEXT statement, but I want to
know why can't work with FOR EACH.

Zhj23


"Gary Keramidas" wrote:

Sub addsheets4()

Dim total As Integer ' <<<<<<<<<<<<<<<<<<<<<added this
Dim rng As Range ' <<<<<<<<<<<<<<<<<<<<<<added this
Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets
Set rng = wkSht.Range("a8:a50") ' <<<<<<<< changed this
total = total + Application.Sum(rng) ' <<<<<changed this

Next wkSht

MsgBox ("Total = ") & total

End Sub


--


Gary


"zhj23" wrote in message
...
Hi! Gary


Yes. I am very interested to know what is wrong with my codes. So when
you
adopt my codes, did it work?

I tried, but it doesn't work. Still the triple of the values in Sheet
1.

Thanks anyway.


zhj23

"Gary Keramidas" wrote:

i adapted your code instead of using mine, in case you're interested

Sub addsheets()
Dim wkSht As Worksheet
Dim rng As Range
total = 0
For Each wkSht In Worksheets
Set rng = wkSht.Range("a8:a50")
total = total + Application.Sum(rng)
Next wkSht
MsgBox ("Total = ") & total
End Sub

--


Gary


"zhj23" wrote in message
...
I have 3 worksheets,wanted to find the total sum of the values
appeared
in
a
column (F say) in each of the worksheets. My following codes do not
give
me
the right answer. Instead it sums only the first sheet, but output
triple
the
total value of the FIRST sheet and ignores the other 2 sheets. Can
anyone
help to resolve this. Many thanks.

Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets

total = total + Application.Sum(Range("f8.f150"))

Next wkSht

MsgBox ("Total = ") & total