Summing a range

[Alan M]

Hi,

I need to use VBA code to define a range and then sum the values of the
cells in that range and place the answer in another cell. So far I have this
but it does not work!

I have defined ReportRange as the range to be summed and RangeValue as Long
to be used for the result

Set ReportRange = Sheets(2).Range(Cells(7, 24), Cells(EndDate, 24))

RangeValue = "=Sum(" & ReportRange.Address(True, False) & ")"


Sheets(16).Select


Sheets(16).Range("d12").Value = RangeValue


Where am I going wrong please?


Alan

[Ardus Petus]

RangeValue = Application.WorksheetFunction.Sum(ReportRange)

HTH
--
AP


"Alan M" <[email protected]> a écrit dans le message de news:
[email protected]...
> Hi,
>
> I need to use VBA code to define a range and then sum the values of the
> cells in that range and place the answer in another cell. So far I have
> this
> but it does not work!
>
> I have defined ReportRange as the range to be summed and RangeValue as
> Long
> to be used for the result
>
> Set ReportRange = Sheets(2).Range(Cells(7, 24), Cells(EndDate, 24))
>
> RangeValue = "=Sum(" & ReportRange.Address(True, False) & ")"
>
>
> Sheets(16).Select
>
>
> Sheets(16).Range("d12").Value = RangeValue
>
>
> Where am I going wrong please?
>
>
> Alan

[Greg Wilson]

Further to Ardus' post, note that the Cells method, if not directly qualified
with the sheet reference, will return the active sheet, not Sheets(2) as
would seem to be implied from this code:

Set ReportRange = Sheets(2).Range(Cells(7, 24), Cells(EndDate, 24))

In other words, "Cells(7, 24)" and "Cells(EndDate, 24)" will return these
cell references on the active sheet and not Sheets(2) in spite of the fact
that you qualified the Range method. In the following code, I use the With
construct to allow abbreviation. Note the preceeding periods in front of
"Range" as well as both instances of "Cells".

With Sheets(2)
Set ReportRange = .Range(.Cells(7, 24), .Cells(EndDate, 24))
End With
RangeValue = Application.Sum(ReportRange)
Sheets(16).Range("d12").Value = RangeValue

Regards,
Greg

"Alan M" wrote:

> Hi,
>
> I need to use VBA code to define a range and then sum the values of the
> cells in that range and place the answer in another cell. So far I have this
> but it does not work!
>
> I have defined ReportRange as the range to be summed and RangeValue as Long
> to be used for the result
>
> Set ReportRange = Sheets(2).Range(Cells(7, 24), Cells(EndDate, 24))
>
> RangeValue = "=Sum(" & ReportRange.Address(True, False) & ")"
>
>
> Sheets(16).Select
>
>
> Sheets(16).Range("d12").Value = RangeValue
>
>
> Where am I going wrong please?
>
>
> Alan