Totalling a variable column of numbers

Topic

New Reply

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

Reply | Quote

Replies

Have you tried the USEDRANGE function?

cheers, Paul

Reply | Quote

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

Reply | Quote

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

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

Hi Alison,

My comments certainly should not be taken as perjorative – they were intended in good humour, hence the ‘!’.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Alison, as a fellow antipodean you should hardly have noticed the direct response of macropod.

cheers, Paul

Reply | Quote

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

Reply | Quote