Sum range of cells with VBA

[sgl]

Hi all,

I need to add a range of cells with VBA whose length
which varies each time.

Within my code I have written the following. Although I
can locate the last cell address within the range the sum
function does not return the value. Can someone please
correct my code or tell me what i am doing wrong.

' Total Debit and Credit columns
Dim TtlDr As Double
Dim TtlCr As Double

Dim LastRowDr As Range
Dim LastRowCr As Range

Set LastRowDr = ActiveWorkbook.Worksheets
("Datasheet").Range("D65536").End(xlUp).Offset(-1, 2)
Set LastRowCr = ActiveWorkbook.Worksheets
("Datasheet").Range("D65536").End(xlUp).Offset(-1, 3)

TtlDr = Application.WorksheetFunction.Sum
(ActiveWorkbook.Worksheets("Datasheet").Range("F8", Range
(LastRowDr.Address)))
TtlCr = Application.WorksheetFunction.Sum
(ActiveWorkbook.Worksheets("Datasheet").Range("G8", Range
(LastRowCr.Address)))

DstRng.Cells(DstR, 6).Value = TtlDr
DstRng.Cells(DstR, 6).Font.Bold = True
DstRng.Cells(DstR, 6).BorderAround Weight:=xlThin

DstRng.Cells(DstR, 7).Value = TtlCr
DstRng.Cells(DstR, 7).Font.Bold = True
DstRng.Cells(DstR, 7).BorderAround Weight:=xlThin

Many thanks in advance

 

[Bob Phillips]

I can see two potential problems in this code

Firstly, the sum function addresses the start range with the
workbook/worksheet qualifier, but not the end range. Try this change

With ActiveWorkbook.Worksheets("DataSheet")
Set LastRowDr = .Range("D65536").End(xlUp).Offset(-1, 2)
Set LastRowCr = .Range("D65536").End(xlUp).Offset(-1, 3)

TtlDr = Application.WorksheetFunction.Sum(.Range("F8",
..Range(LastRowDr.Address)))
TtlCr = Application.WorksheetFunction.Sum(.Range("G8",
..Range(LastRowCr.Address)))
End With

Secondly, the range DstRng doesn't seem to get set to anything.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[sgl]

Bob your suggestion worked a treat.

The code posted is only a snippet from a long piece of
VBA coding and the DstR has been set much earlier. The
part i could not get to work was the sum function. I now
clearly see my ommission and the summ function works
perfectly.

Thanks for you very quick response!.

 

[Bob Phillips]

No probs, glad to help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)