PC Review


Reply
Thread Tools Rate Thread

Sum range of cells with VBA

 
 
sgl
Guest
Posts: n/a
 
      6th Feb 2004
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
 
Reply With Quote
 
 
 
 
Bob Phillips
Guest
Posts: n/a
 
      6th Feb 2004
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" <(E-Mail Removed)> wrote in message
news:be8e01c3ecbd$20a78750$(E-Mail Removed)...
> 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



 
Reply With Quote
 
 
 
 
sgl
Guest
Posts: n/a
 
      6th Feb 2004
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!.

>-----Original Message-----
>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" <(E-Mail Removed)> wrote in

message
>news:be8e01c3ecbd$20a78750$(E-Mail Removed)...
>> 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

>
>
>.
>

 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      6th Feb 2004
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)

"sgl" <(E-Mail Removed)> wrote in message
news:bc5f01c3eccb$4c13d430$(E-Mail Removed)...
> 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!.
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum If range of dates date range, sum totals Stilmovin Microsoft Excel Worksheet Functions 7 16th Dec 2008 06:49 PM
sum range of cells using cells, not range rdarling@southwickclothing.com Microsoft Excel Programming 4 28th Mar 2007 01:50 PM
Copy a formula to a range of cells via VB6 using .Range(Cells(row,col), Cells(row,col)).Formula= statement Kevin Microsoft Excel Programming 7 5th Oct 2004 08:11 PM
Is there a range function (i.e. Max(range of cells)-Min(range of cells))?? ModelerGirl Microsoft Excel Misc 3 26th Jul 2004 10:12 PM
Sum Range 1 when the elements in Range 2 are found in Range 3 james s shoenfelt Microsoft Excel Misc 1 4th Nov 2003 12:34 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:12 PM.