Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    London UK, Hertfordshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am writing a macro that does formats a download of data that we have so it is as our Client's require. I am very proud of this macro except I cannot get it to total some columns.

    The columns are always in the same place (BI and BJ). The macro inserts a column after BJ and then I need it to:

    Add BI2 and BJ2 together into BK
    Copy the addition formula all the way down to the last row (which varies on each download - could be 2, could be thousands).
    Add a total to the bottom of BI, BJ and the new BK

    The macro then does some other stuff. I have managed in a very clunky way to get the first two sentences above to work, but it is very clunky. I cannot get the last sentence at all as it will not adjust according to the number of rows.

    Can anyone help me as I am sure I am missing something fundamental, I can do this easily when working live but my macro will not replicate even though I am careful with my relative and absolute referencing. I am not a developer, I am an advanced excel user who helps out others in my Company but the word 'advanced' is subjective obviously.

    Cheers
    Kate

  2. #2
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,614
    Thanks
    7
    Thanked 230 Times in 218 Posts
    Have you tried the USEDRANGE function?

    cheers, Paul

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Sub GetAddedUp()
    Dim LastRow As Long
    LastRow = Application.WorksheetFunction.Max _
    (Cells(Rows.Count, 9).End(xlUp).Row, _
    Cells(Rows.Count, 10).End(xlUp).Row)

    Cells(LastRow + 2, 9).Formula = "=Sum(I2:I" & LastRow & ")"
    Cells(LastRow + 2, 10).Formula = "=Sum(J2:J" & LastRow & ")"

    Cells(LastRow + 2, 11).Formula = "=If(Sum(I" & LastRow + 2 & _
    ":J" & LastRow + 2 & ")<>sum(K2:K" & LastRow & _
    "),""Check"",Sum(I" & LastRow + 2 & ":J" & LastRow + 2 & "))"
    End Sub
    '--
    Jim Cone
    Portland, Oregon USA
    Special Sort Excel add-in

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,157
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Kate,

    Try:
    Code:
    [BK2].Select
    Do While ActiveCell.Offset(0,-2).Value <> ""
      ActiveCell.FormulaR1C1="=RC[-2]+RC[-1]"
      ActiveCell.Offset(1,0).Select
    Loop
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Quote Originally Posted by RetiredGeek View Post
    Kate,

    Try:
    Code:
    [BK2].Select
    Do While ActiveCell.Offset(0,-2).Value <> ""
      ActiveCell.FormulaR1C1="=RC[-2]+RC[-1]"
      ActiveCell.Offset(1,0).Select
    Loop
    That's real ugly code - all those unnecessary Select and Offset operations!
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by macropod View Post
    That's real ugly code - all those unnecessary Select and Offset operations!
    Macropod - I was a bit surprised by the peremptory tone of your post! As a Silver Lounger/Moderator addressing a Four-star Lounger in a public forum, perhaps you could just have pointed out that Jim Cone's solution would process more efficiently by avoiding the need to select each cell in turn - making essentially the same point, but in a less pejorative manner.

    Whenever I post a question I browse to see if I can answer any outstanding posts by way of doing my bit in return; but I usually find someone's got there before me, and rarely find unanswered posts, let alone any that I can help with. I'm sure if I do post a solution there is likely to be someone who knows better, but in that case would prefer to be pointed in the right direction than just told I have it wrong.

    Cheers

    Alison C

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Hi Alison,

    My comments certainly should not be taken as perjorative - they were intended in good humour, hence the '!'.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    New Lounger
    Join Date
    Jun 2002
    Location
    London UK, Hertfordshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I appreciate your help but I cannot get it to work. I am unable to post the whole macro up (It is long and clunky, as I said I am not a developer and am reluctant to 'clean' code until it works but more importantly it has confidential info in it) but I can show the preceeding and following code:

    Preceding
    Range("BK1").Select
    Selection.EntireColumn.Insert
    ActiveCell.FormulaR1C1 = "Grand Total"
    With ActiveCell.Characters(Start:=1, Length:=11).Font
    .FontStyle = "Bold"
    End With
    Range("BK2").Select


    Following
    Range("BL1:BV1").Select
    Selection.EntireColumn.Hidden = True
    Range("BW1:BX1").Select
    Selection.EntireColumn.Delete

    What am I doing wrong? Appreciate your help, I am learning

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,891
    Thanks
    0
    Thanked 81 Times in 77 Posts
    Based on Jim's code and yours:

    Code:
    ****With Range("BK1")
    ****** .EntireColumn.Insert
    ****** .Value = "Grand Total"
    ****** .Font.Bold = True
    ****End With
    ****
    ****Dim LastRow As Long
    ** * ' get highest used row number from cols BI and BJ
    ****LastRow = Application.WorksheetFunction.Max _
    ****(Cells(Rows.Count, "BI").End(xlUp).Row, _
    ****Cells(Rows.Count, "BJ").End(xlUp).Row)
    ****
    ****' add sum formulas to BK
    ****Range("BK2:BK" & LastRow).FormulaR1C1 = "=Sum(RC[-2]:RC[-1]"
    ****' add totals to the row beneath the last row for BI:BK
    ****Range("BI" & LastRow + 1).Resize(, 3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
    
    
    
    ****Range("BL1:BV1").EntireColumn.Hidden = True
    ****Range("BW1:BX1").EntireColumn.Delete
    Regards,
    Rory
    Microsoft MVP - Excel.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,614
    Thanks
    7
    Thanked 230 Times in 218 Posts
    Alison, as a fellow antipodean you should hardly have noticed the direct response of macropod.

    cheers, Paul

  11. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    You're right, I should by now be used to direct talking with no unnecessary words - must remember it's all said in the !



    Cheers

    Alison

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •