Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating an overdue report. | New Users to Excel | |||
Creating a Report | Excel Discussion (Misc queries) | |||
Creating a report with .jpg files in it | Excel Discussion (Misc queries) | |||
Creating a Report in Excel | Excel Discussion (Misc queries) | |||
Creating a Subset Report | Excel Worksheet Functions |