Creating a report by For Each...
 

I gather information from various, named ranges, in a number of worksheets in
a specific workbook. I use a "For Each loop" to search in every sheet for
values in defined ranges and then place that value in a summary report. This
works fine with one single value in that range. However, one of these ranges,
a list, contains numbers that I want to sum up and place in the report.

This is part of the code
....
Next rn
If rnExists = True Then
With totalSheet
On Error Resume Next
.Cells(1, i).Value = sName
.Cells(2, i).Value = Range(sName & "!Database").Rows.Count
.Cells(3, i).Value = "=Sum(Range(sName & !sumPremiums))"
End With
i = i + 1
Else
....

It is the third .Cells-argument that causes my problem. Would be happy for
any solution.
--
Stef

Creating a report by For Each...
 

Hi Stef,


.Cells(3, i).Value = "=Sum(Range(sName & !
sumPremiums))"

It is the third .Cells-argument that causes my problem.



Try .cells(3,i).Formula instead of .Value...

Best

Markus

Creating a report by For Each...
 

Assuming you want a formula like =Sum(Sheet1!sumPremiums)

where sumPremiums is a sheet level name:

.Cells(3, i).Value = "=Sum('" & sName & "'!sumPremiums)"

--
Regards,
Tom Ogilvy

"Stef" wrote in message
...
I gather information from various, named ranges, in a number of worksheets
in
a specific workbook. I use a "For Each loop" to search in every sheet for
values in defined ranges and then place that value in a summary report.
This
works fine with one single value in that range. However, one of these
ranges,
a list, contains numbers that I want to sum up and place in the report.

This is part of the code
...
Next rn
If rnExists = True Then
With totalSheet
On Error Resume Next
.Cells(1, i).Value = sName
.Cells(2, i).Value = Range(sName & "!Database").Rows.Count
.Cells(3, i).Value = "=Sum(Range(sName & !sumPremiums))"
End With
i = i + 1
Else
...

It is the third .Cells-argument that causes my problem. Would be happy
for
any solution.
--
Stef

Creating a report by For Each...
 

Hi Markus!

I tried that without success. Thanks anyway.

Stef

"Markus Scheible" wrote:

Hi Stef,


.Cells(3, i).Value = "=Sum(Range(sName & !
sumPremiums))"

It is the third .Cells-argument that causes my problem.



Try .cells(3,i).Formula instead of .Value...

Best

Markus

Creating a report by For Each...
 

Hi Stef,

I tried that without success. Thanks anyway.

okay... so what exactly have you defined as sumPremiums?
Is it a range of cells you want to sum?

Best

Markus



.Cells(3, i).Value = "=Sum(Range(sName & !
sumPremiums))"

It is the third .Cells-argument that causes my
problem.



Try .cells(3,i).Formula instead of .Value...

Best

Markus

.

Creating a report by For Each...
 

Actually, I want the sum of numbers in a column that I named "sumPremiums" to
be placed in my report the same way I do with the other .Cells arguments. So
the formula itself is not important, only the result from that calculation.

Regards Stef

"Tom Ogilvy" wrote:

Assuming you want a formula like =Sum(Sheet1!sumPremiums)

where sumPremiums is a sheet level name:

.Cells(3, i).Value = "=Sum('" & sName & "'!sumPremiums)"

--
Regards,
Tom Ogilvy

"Stef" wrote in message
...
I gather information from various, named ranges, in a number of worksheets
in
a specific workbook. I use a "For Each loop" to search in every sheet for
values in defined ranges and then place that value in a summary report.
This
works fine with one single value in that range. However, one of these
ranges,
a list, contains numbers that I want to sum up and place in the report.

This is part of the code
...
Next rn
If rnExists = True Then
With totalSheet
On Error Resume Next
.Cells(1, i).Value = sName
.Cells(2, i).Value = Range(sName & "!Database").Rows.Count
.Cells(3, i).Value = "=Sum(Range(sName & !sumPremiums))"
End With
i = i + 1
Else
...

It is the third .Cells-argument that causes my problem. Would be happy
for
any solution.
--
Stef

Creating a report by For Each...
 

Hi Stef,


Actually, I want the sum of numbers in a column that I
named "sumPremiums" to
be placed in my report the same way I do with the
other .Cells arguments. So
the formula itself is not important, only the result from
that calculation.

so then, why not use

Cells(3,i).Value = Sum(Sheets(sName).Range(sumpremiums) ?

Best

Markus



Regards Stef

"Tom Ogilvy" wrote:

Assuming you want a formula like =Sum(Sheet1!
sumPremiums)

where sumPremiums is a sheet level name:

.Cells(3, i).Value = "=Sum('" & sName & "'!
sumPremiums)"

--
Regards,
Tom Ogilvy

"Stef" wrote in message
news:4133E996-64AE-4A01-9D95-
...
I gather information from various, named ranges, in a
number of worksheets
in
a specific workbook. I use a "For Each loop" to
search in every sheet for
values in defined ranges and then place that value in
a summary report.
This
works fine with one single value in that range.
However, one of these
ranges,
a list, contains numbers that I want to sum up and
place in the report.

This is part of the code
...
Next rn
If rnExists = True Then
With totalSheet
On Error Resume Next
.Cells(1, i).Value = sName
.Cells(2, i).Value = Range(sName & "!
Database").Rows.Count
.Cells(3, i).Value = "=Sum(Range(sName & !
sumPremiums))"
End With
i = i + 1
Else
...

It is the third .Cells-argument that causes my
problem. Would be happy
for
any solution.
--
Stef



.

Creating a report by For Each...
 

.Cells(3, i).Value =Application.Sum(Range(sName & "!sumPremiums"))

--
Regards,
Tom Ogilvy

"Stef" wrote in message
...
Actually, I want the sum of numbers in a column that I named "sumPremiums"
to
be placed in my report the same way I do with the other .Cells arguments.
So
the formula itself is not important, only the result from that
calculation.

Regards Stef

"Tom Ogilvy" wrote:

Assuming you want a formula like =Sum(Sheet1!sumPremiums)

where sumPremiums is a sheet level name:

.Cells(3, i).Value = "=Sum('" & sName & "'!sumPremiums)"

--
Regards,
Tom Ogilvy

"Stef" wrote in message
...
I gather information from various, named ranges, in a number of
worksheets
in
a specific workbook. I use a "For Each loop" to search in every sheet
for
values in defined ranges and then place that value in a summary
report.
This
works fine with one single value in that range. However, one of these
ranges,
a list, contains numbers that I want to sum up and place in the
report.

This is part of the code
...
Next rn
If rnExists = True Then
With totalSheet
On Error Resume Next
.Cells(1, i).Value = sName
.Cells(2, i).Value = Range(sName & "!Database").Rows.Count
.Cells(3, i).Value = "=Sum(Range(sName & !sumPremiums))"
End With
i = i + 1
Else
...

It is the third .Cells-argument that causes my problem. Would be
happy
for
any solution.
--
Stef

Creating a report by For Each...
 

Thanks Tom,
that made It!!

Stef

"Tom Ogilvy" wrote:

.Cells(3, i).Value =Application.Sum(Range(sName & "!sumPremiums"))

--
Regards,
Tom Ogilvy

"Stef" wrote in message
...
Actually, I want the sum of numbers in a column that I named "sumPremiums"
to
be placed in my report the same way I do with the other .Cells arguments.
So
the formula itself is not important, only the result from that
calculation.

Regards Stef

"Tom Ogilvy" wrote:

Assuming you want a formula like =Sum(Sheet1!sumPremiums)

where sumPremiums is a sheet level name:

.Cells(3, i).Value = "=Sum('" & sName & "'!sumPremiums)"

--
Regards,
Tom Ogilvy

"Stef" wrote in message
...
I gather information from various, named ranges, in a number of
worksheets
in
a specific workbook. I use a "For Each loop" to search in every sheet
for
values in defined ranges and then place that value in a summary
report.
This
works fine with one single value in that range. However, one of these
ranges,
a list, contains numbers that I want to sum up and place in the
report.

This is part of the code
...
Next rn
If rnExists = True Then
With totalSheet
On Error Resume Next
.Cells(1, i).Value = sName
.Cells(2, i).Value = Range(sName & "!Database").Rows.Count
.Cells(3, i).Value = "=Sum(Range(sName & !sumPremiums))"
End With
i = i + 1
Else
...

It is the third .Cells-argument that causes my problem. Would be
happy
for
any solution.
--
Stef