Sum range of cells with VBA

Discussion in 'Microsoft Excel Misc' started by sgl, Feb 6, 2004.

  1. sgl

    sgl Guest

    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
     
    sgl, Feb 6, 2004
    #1
    1. Advertisements

  2. sgl

    Bob Phillips Guest

    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" <> wrote in message
    news:be8e01c3ecbd$20a78750$...
    > 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, Feb 6, 2004
    #2
    1. Advertisements

  3. sgl

    sgl Guest

    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" <> wrote in

    message
    >news:be8e01c3ecbd$20a78750$...
    >> 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

    >
    >
    >.
    >
     
    sgl, Feb 6, 2004
    #3
  4. sgl

    Bob Phillips Guest

    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" <> wrote in message
    news:bc5f01c3eccb$4c13d430$...
    > 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, Feb 6, 2004
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JMay
    Replies:
    3
    Views:
    1,219
  2. james s shoenfelt

    Sum Range 1 when the elements in Range 2 are found in Range 3

    james s shoenfelt, Nov 3, 2003, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    265
    Don Guillett
    Nov 3, 2003
  3. ModelerGirl
    Replies:
    3
    Views:
    525
  4. Guest
    Replies:
    3
    Views:
    306
    Domenic
    Feb 24, 2006
  5. tkw
    Replies:
    2
    Views:
    321
    Dave Peterson
    Oct 1, 2009
Loading...

Share This Page