FOR EACH statement

[Gary''s Student]

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

[zhj23]

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

[Gary''s Student]

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

[zhj23]

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 Keramidas]

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" <zhj23@discussions.microsoft.com> wrote in message
news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
>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 Keramidas]

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

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:OO4rRwPzFHA.908@tk2msftngp13.phx.gbl...
> 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" <zhj23@discussions.microsoft.com> wrote in message
> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
>>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 Keramidas]

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" <zhj23@discussions.microsoft.com> wrote in message
news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
>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

[zhj23]

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" <zhj23@discussions.microsoft.com> wrote in message
> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
> >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 Keramidas]

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" <zhj23@discussions.microsoft.com> wrote in message
news:867A8FCA-3669-4E18-99A8-364A576B026C@microsoft.com...
> 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" <zhj23@discussions.microsoft.com> wrote in message
>> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
>> >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
>>
>>
>>

[zhj23]

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" <zhj23@discussions.microsoft.com> wrote in message
> news:867A8FCA-3669-4E18-99A8-364A576B026C@microsoft.com...
> > 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" <zhj23@discussions.microsoft.com> wrote in message
> >> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
> >> >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 Keramidas]

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" <zhj23@discussions.microsoft.com> wrote in message
news:C46AE9B4-85A2-4D2B-B3AB-C9D86F5BFCBD@microsoft.com...
> 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" <zhj23@discussions.microsoft.com> wrote in message
>> news:867A8FCA-3669-4E18-99A8-364A576B026C@microsoft.com...
>> > 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" <zhj23@discussions.microsoft.com> wrote in message
>> >> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>

[zhj23]

Gary

I did all you told, except my data is in Col F, which I don't think it is
material.

Yes. I got 3* the total of Sheet 1 ONLY.

(and i don't think the dot and colon in the range matter)

Below are my codes.


Sub Total_test()

Dim wkSht As Worksheet
Dim rnge As Range
Dim total As Integer

total = 0
For Each wkSht In Worksheets
Set rnge = wkSht.Range("f8.f150")
total = total + Application.Sum(rnge)

Next wkSht

MsgBox ("Total = ") & total

End Sub






"Gary Keramidas" wrote:

> 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" <zhj23@discussions.microsoft.com> wrote in message
> news:C46AE9B4-85A2-4D2B-B3AB-C9D86F5BFCBD@microsoft.com...
> > 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" <zhj23@discussions.microsoft.com> wrote in message
> >> news:867A8FCA-3669-4E18-99A8-364A576B026C@microsoft.com...
> >> > 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" <zhj23@discussions.microsoft.com> wrote in message
> >> >> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
> >> >> >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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Dave Peterson]

What makes you think that the total really isn't just 3 * the total in sheet1.
Maybe it's just a coincidence???

Option Explicit

Sub Total_test()

Dim wkSht As Worksheet
Dim rnge As Range
Dim total As Double

total = 0
For Each wkSht In Worksheets
Set rnge = wkSht.Range("f8:f150")
total = total + Application.Sum(rnge)
MsgBox "Sheet " & wkSht.Name & " total: " & Application.Sum(rnge) _
& vbLf & "Running total: " & total
Next wkSht

MsgBox ("Total = ") & total

End Sub

zhj23 wrote:
>
> Gary
>
> I did all you told, except my data is in Col F, which I don't think it is
> material.
>
> Yes. I got 3* the total of Sheet 1 ONLY.
>
> (and i don't think the dot and colon in the range matter)
>
> Below are my codes.
>
> Sub Total_test()
>
> Dim wkSht As Worksheet
> Dim rnge As Range
> Dim total As Integer
>
> total = 0
> For Each wkSht In Worksheets
> Set rnge = wkSht.Range("f8.f150")
> total = total + Application.Sum(rnge)
>
> Next wkSht
>
> MsgBox ("Total = ") & total
>
> End Sub
>
> "Gary Keramidas" wrote:
>
> > 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" <zhj23@discussions.microsoft.com> wrote in message
> > news:C46AE9B4-85A2-4D2B-B3AB-C9D86F5BFCBD@microsoft.com...
> > > 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" <zhj23@discussions.microsoft.com> wrote in message
> > >> news:867A8FCA-3669-4E18-99A8-364A576B026C@microsoft.com...
> > >> > 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" <zhj23@discussions.microsoft.com> wrote in message
> > >> >> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
> > >> >> >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
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

--

Dave Peterson

[Gary Keramidas]

i was about to ask what the individual totals were for each sheet. dave, one
question, what's the significance of total as double?

--


Gary


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4349C343.B705210C@verizonXSPAM.net...
> What makes you think that the total really isn't just 3 * the total in
> sheet1.
> Maybe it's just a coincidence???
>
> Option Explicit
>
> Sub Total_test()
>
> Dim wkSht As Worksheet
> Dim rnge As Range
> Dim total As Double
>
> total = 0
> For Each wkSht In Worksheets
> Set rnge = wkSht.Range("f8:f150")
> total = total + Application.Sum(rnge)
> MsgBox "Sheet " & wkSht.Name & " total: " & Application.Sum(rnge) _
> & vbLf & "Running total: " & total
> Next wkSht
>
> MsgBox ("Total = ") & total
>
> End Sub
>
> zhj23 wrote:
>>
>> Gary
>>
>> I did all you told, except my data is in Col F, which I don't think it is
>> material.
>>
>> Yes. I got 3* the total of Sheet 1 ONLY.
>>
>> (and i don't think the dot and colon in the range matter)
>>
>> Below are my codes.
>>
>> Sub Total_test()
>>
>> Dim wkSht As Worksheet
>> Dim rnge As Range
>> Dim total As Integer
>>
>> total = 0
>> For Each wkSht In Worksheets
>> Set rnge = wkSht.Range("f8.f150")
>> total = total + Application.Sum(rnge)
>>
>> Next wkSht
>>
>> MsgBox ("Total = ") & total
>>
>> End Sub
>>
>> "Gary Keramidas" wrote:
>>
>> > 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" <zhj23@discussions.microsoft.com> wrote in message
>> > news:C46AE9B4-85A2-4D2B-B3AB-C9D86F5BFCBD@microsoft.com...
>> > > 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" <zhj23@discussions.microsoft.com> wrote in message
>> > >> news:867A8FCA-3669-4E18-99A8-364A576B026C@microsoft.com...
>> > >> > 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" <zhj23@discussions.microsoft.com> wrote in message
>> > >> >> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
>> > >> >> >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
>> > >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >
>
> --
>
> Dave Peterson

[Dave Peterson]

An integer is a whole number (..., -3,-2, -1, 0, 1, 2, 3, ...)

A double can contain decimals (3.14159, 1.41, 0, 1.01001000100001)



Gary Keramidas wrote:
>
> i was about to ask what the individual totals were for each sheet. dave, one
> question, what's the significance of total as double?
>
> --
>
> Gary
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:4349C343.B705210C@verizonXSPAM.net...
> > What makes you think that the total really isn't just 3 * the total in
> > sheet1.
> > Maybe it's just a coincidence???
> >
> > Option Explicit
> >
> > Sub Total_test()
> >
> > Dim wkSht As Worksheet
> > Dim rnge As Range
> > Dim total As Double
> >
> > total = 0
> > For Each wkSht In Worksheets
> > Set rnge = wkSht.Range("f8:f150")
> > total = total + Application.Sum(rnge)
> > MsgBox "Sheet " & wkSht.Name & " total: " & Application.Sum(rnge) _
> > & vbLf & "Running total: " & total
> > Next wkSht
> >
> > MsgBox ("Total = ") & total
> >
> > End Sub
> >
> > zhj23 wrote:
> >>
> >> Gary
> >>
> >> I did all you told, except my data is in Col F, which I don't think it is
> >> material.
> >>
> >> Yes. I got 3* the total of Sheet 1 ONLY.
> >>
> >> (and i don't think the dot and colon in the range matter)
> >>
> >> Below are my codes.
> >>
> >> Sub Total_test()
> >>
> >> Dim wkSht As Worksheet
> >> Dim rnge As Range
> >> Dim total As Integer
> >>
> >> total = 0
> >> For Each wkSht In Worksheets
> >> Set rnge = wkSht.Range("f8.f150")
> >> total = total + Application.Sum(rnge)
> >>
> >> Next wkSht
> >>
> >> MsgBox ("Total = ") & total
> >>
> >> End Sub
> >>
> >> "Gary Keramidas" wrote:
> >>
> >> > 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" <zhj23@discussions.microsoft.com> wrote in message
> >> > news:C46AE9B4-85A2-4D2B-B3AB-C9D86F5BFCBD@microsoft.com...
> >> > > 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" <zhj23@discussions.microsoft.com> wrote in message
> >> > >> news:867A8FCA-3669-4E18-99A8-364A576B026C@microsoft.com...
> >> > >> > 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" <zhj23@discussions.microsoft.com> wrote in message
> >> > >> >> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
> >> > >> >> >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
> >> > >> >>
> >> > >> >>
> >> > >> >>
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >
> >> >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[zhj23]

Dave:

With your codes I got the answer.

Yes. it is indeed a coincidence which dirves me crazy.

However, i have one more question:

If I use this statement, and drop the SET statement

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

It indeed gives me 3*total Sheet 1. (bcos I hv 3 sheets?)

Could you enlighten me. Thanks.

(Gary: sorry for taken so much of your time resulting from the COINCIDENCE)


Zhj23




"Dave Peterson" wrote:

> What makes you think that the total really isn't just 3 * the total in sheet1.
> Maybe it's just a coincidence???
>
> Option Explicit
>
> Sub Total_test()
>
> Dim wkSht As Worksheet
> Dim rnge As Range
> Dim total As Double
>
> total = 0
> For Each wkSht In Worksheets
> Set rnge = wkSht.Range("f8:f150")
> total = total + Application.Sum(rnge)
> MsgBox "Sheet " & wkSht.Name & " total: " & Application.Sum(rnge) _
> & vbLf & "Running total: " & total
> Next wkSht
>
> MsgBox ("Total = ") & total
>
> End Sub
>
> zhj23 wrote:
> >
> > Gary
> >
> > I did all you told, except my data is in Col F, which I don't think it is
> > material.
> >
> > Yes. I got 3* the total of Sheet 1 ONLY.
> >
> > (and i don't think the dot and colon in the range matter)
> >
> > Below are my codes.
> >
> > Sub Total_test()
> >
> > Dim wkSht As Worksheet
> > Dim rnge As Range
> > Dim total As Integer
> >
> > total = 0
> > For Each wkSht In Worksheets
> > Set rnge = wkSht.Range("f8.f150")
> > total = total + Application.Sum(rnge)
> >
> > Next wkSht
> >
> > MsgBox ("Total = ") & total
> >
> > End Sub
> >
> > "Gary Keramidas" wrote:
> >
> > > 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" <zhj23@discussions.microsoft.com> wrote in message
> > > news:C46AE9B4-85A2-4D2B-B3AB-C9D86F5BFCBD@microsoft.com...
> > > > 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" <zhj23@discussions.microsoft.com> wrote in message
> > > >> news:867A8FCA-3669-4E18-99A8-364A576B026C@microsoft.com...
> > > >> > 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" <zhj23@discussions.microsoft.com> wrote in message
> > > >> >> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
> > > >> >> >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
> > > >> >>
> > > >> >>
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

[Gary Keramidas]

don't worry about taking up my time.

your line of code refers to sheet 1 even if you activate the next sheet.

that's why i added the wksht. in front changed it to Set rng =
wkSht.Range("a8:a50")
this way it refers to the range on the active sheet.

you could also use this if you don't want to use the set statement

total = total + Application.Sum(wkSht.Range("a8:a50"))

so it would look like this


Dim total As Double
Dim rng As Range
Dim wkSht As Worksheet
total = 0
For Each wkSht In Worksheets
total = total + Application.Sum(wkSht.Range("a8:a50"))
Next wkSht

MsgBox ("Total = ") & total

End Sub

--


Gary


"zhj23" <zhj23@discussions.microsoft.com> wrote in message
news:FF6772D8-B1EE-4C0A-B0C0-74AFBA648E33@microsoft.com...
> Dave:
>
> With your codes I got the answer.
>
> Yes. it is indeed a coincidence which dirves me crazy.
>
> However, i have one more question:
>
> If I use this statement, and drop the SET statement
>
> total = total + Application.Sum(Range("f8.f150"))
>
> It indeed gives me 3*total Sheet 1. (bcos I hv 3 sheets?)
>
> Could you enlighten me. Thanks.
>
> (Gary: sorry for taken so much of your time resulting from the
> COINCIDENCE)
>
>
> Zhj23
>
>
>
>
> "Dave Peterson" wrote:
>
>> What makes you think that the total really isn't just 3 * the total in
>> sheet1.
>> Maybe it's just a coincidence???
>>
>> Option Explicit
>>
>> Sub Total_test()
>>
>> Dim wkSht As Worksheet
>> Dim rnge As Range
>> Dim total As Double
>>
>> total = 0
>> For Each wkSht In Worksheets
>> Set rnge = wkSht.Range("f8:f150")
>> total = total + Application.Sum(rnge)
>> MsgBox "Sheet " & wkSht.Name & " total: " & Application.Sum(rnge)
>> _
>> & vbLf & "Running total: " & total
>> Next wkSht
>>
>> MsgBox ("Total = ") & total
>>
>> End Sub
>>
>> zhj23 wrote:
>> >
>> > Gary
>> >
>> > I did all you told, except my data is in Col F, which I don't think it
>> > is
>> > material.
>> >
>> > Yes. I got 3* the total of Sheet 1 ONLY.
>> >
>> > (and i don't think the dot and colon in the range matter)
>> >
>> > Below are my codes.
>> >
>> > Sub Total_test()
>> >
>> > Dim wkSht As Worksheet
>> > Dim rnge As Range
>> > Dim total As Integer
>> >
>> > total = 0
>> > For Each wkSht In Worksheets
>> > Set rnge = wkSht.Range("f8.f150")
>> > total = total + Application.Sum(rnge)
>> >
>> > Next wkSht
>> >
>> > MsgBox ("Total = ") & total
>> >
>> > End Sub
>> >
>> > "Gary Keramidas" wrote:
>> >
>> > > 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" <zhj23@discussions.microsoft.com> wrote in message
>> > > news:C46AE9B4-85A2-4D2B-B3AB-C9D86F5BFCBD@microsoft.com...
>> > > > 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" <zhj23@discussions.microsoft.com> wrote in message
>> > > >> news:867A8FCA-3669-4E18-99A8-364A576B026C@microsoft.com...
>> > > >> > 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" <zhj23@discussions.microsoft.com> wrote in message
>> > > >> >> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
>> > > >> >> >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
>> > > >> >>
>> > > >> >>
>> > > >> >>
>> > > >>
>> > > >>
>> > > >>
>> > >
>> > >
>> > >
>>
>> --
>>
>> Dave Peterson
>>

[zhj23]

Gary & Dave

A big thank you. It is clear now.

zhj23

"Gary Keramidas" wrote:

> don't worry about taking up my time.
>
> your line of code refers to sheet 1 even if you activate the next sheet.
>
> that's why i added the wksht. in front changed it to Set rng =
> wkSht.Range("a8:a50")
> this way it refers to the range on the active sheet.
>
> you could also use this if you don't want to use the set statement
>
> total = total + Application.Sum(wkSht.Range("a8:a50"))
>
> so it would look like this
>
>
> Dim total As Double
> Dim rng As Range
> Dim wkSht As Worksheet
> total = 0
> For Each wkSht In Worksheets
> total = total + Application.Sum(wkSht.Range("a8:a50"))
> Next wkSht
>
> MsgBox ("Total = ") & total
>
> End Sub
>
> --
>
>
> Gary
>
>
> "zhj23" <zhj23@discussions.microsoft.com> wrote in message
> news:FF6772D8-B1EE-4C0A-B0C0-74AFBA648E33@microsoft.com...
> > Dave:
> >
> > With your codes I got the answer.
> >
> > Yes. it is indeed a coincidence which dirves me crazy.
> >
> > However, i have one more question:
> >
> > If I use this statement, and drop the SET statement
> >
> > total = total + Application.Sum(Range("f8.f150"))
> >
> > It indeed gives me 3*total Sheet 1. (bcos I hv 3 sheets?)
> >
> > Could you enlighten me. Thanks.
> >
> > (Gary: sorry for taken so much of your time resulting from the
> > COINCIDENCE)
> >
> >
> > Zhj23
> >
> >
> >
> >
> > "Dave Peterson" wrote:
> >
> >> What makes you think that the total really isn't just 3 * the total in
> >> sheet1.
> >> Maybe it's just a coincidence???
> >>
> >> Option Explicit
> >>
> >> Sub Total_test()
> >>
> >> Dim wkSht As Worksheet
> >> Dim rnge As Range
> >> Dim total As Double
> >>
> >> total = 0
> >> For Each wkSht In Worksheets
> >> Set rnge = wkSht.Range("f8:f150")
> >> total = total + Application.Sum(rnge)
> >> MsgBox "Sheet " & wkSht.Name & " total: " & Application.Sum(rnge)
> >> _
> >> & vbLf & "Running total: " & total
> >> Next wkSht
> >>
> >> MsgBox ("Total = ") & total
> >>
> >> End Sub
> >>
> >> zhj23 wrote:
> >> >
> >> > Gary
> >> >
> >> > I did all you told, except my data is in Col F, which I don't think it
> >> > is
> >> > material.
> >> >
> >> > Yes. I got 3* the total of Sheet 1 ONLY.
> >> >
> >> > (and i don't think the dot and colon in the range matter)
> >> >
> >> > Below are my codes.
> >> >
> >> > Sub Total_test()
> >> >
> >> > Dim wkSht As Worksheet
> >> > Dim rnge As Range
> >> > Dim total As Integer
> >> >
> >> > total = 0
> >> > For Each wkSht In Worksheets
> >> > Set rnge = wkSht.Range("f8.f150")
> >> > total = total + Application.Sum(rnge)
> >> >
> >> > Next wkSht
> >> >
> >> > MsgBox ("Total = ") & total
> >> >
> >> > End Sub
> >> >
> >> > "Gary Keramidas" wrote:
> >> >
> >> > > 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" <zhj23@discussions.microsoft.com> wrote in message
> >> > > news:C46AE9B4-85A2-4D2B-B3AB-C9D86F5BFCBD@microsoft.com...
> >> > > > 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" <zhj23@discussions.microsoft.com> wrote in message
> >> > > >> news:867A8FCA-3669-4E18-99A8-364A576B026C@microsoft.com...
> >> > > >> > 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" <zhj23@discussions.microsoft.com> wrote in message
> >> > > >> >> news:667AD4AB-5F6B-4414-8B69-50CA93E9374B@microsoft.com...
> >> > > >> >> >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
> >> > > >> >>
> >> > > >> >>
> >> > > >> >>
> >> > > >>
> >> > > >>
> >> > > >>
> >> > >
> >> > >
> >> > >
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
>