How to get "Total" of few rows in VBA

[Priyantha]

Dear All,


I am trying to get sum of few rows value in to "Total" Row. I tried below code not working (Seems #Name).

Dim lastCol As Long, startrow As Integer, col As Integer

lastCol = Cells(2, Columns.Count).End(xlToLeft).Column
startrow = 3

lr = 2
Do While wb.Worksheets("ArrearsShedule").Cells(lr + 1, 2).Value <> ""
lr = lr + 1
Loop

For col = 7 To lastCol

''wb.Worksheets("ArrearsShedule").Cells(5, col).Formula = "=Sum(E5:E" & lr + 1 & ")"
wb.Worksheets("ArrearsShedule").Cells(5, col).Formula = "=Sum(Cells(" & startrow & ", " & col & " ):Cells(" & col & " , " & lr & "))"
Next col

please help me.

Br,

Priyantha

[HansV]

You cannot use Cells in a formula. This may work:

Code: Select all

    wb.Worksheets("ArrearsShedule").Cells(5, col).FormulaR1C1 = "=SUM(R" & startrow & "C" & col & ":R" & col & "C" & lr & ")"

but I cannot fully test the code.

[Priyantha]

Dear Hasns,

That Code working,

Thanks,

Priyantha

[p45cal]

Looking only at the Arrears Shedule.xlsm file, only minimally changing your code:

Code: Select all

With wb.Worksheets("ArrearsShedule")
  lastcol = .Cells(2, .Columns.Count).End(xlToLeft).Column
  startrow = 3
  lr = 2
  Do While .Cells(lr + 1, 2).Value <> ""
    lr = lr + 1
  Loop
  Range(.Cells(lr + 1, "G"), Cells(lr + 1, lastcol)).FormulaR1C1 = "=SUM(R" & startrow & "C:R" & lr & "C)"
End With

I've assumed that you set wb somewhere. (Very similar to Hans' solution)

[DocAElstein]

Hi,

I've assumed that you set wb somewhere. ….

It’s in the workbook 2024-04-28.xlsm
, in module Save
, in Sub Increment_SalaryArrears_Save() , towards the start where it opens Arrears Shedule.xlsm .
Towards the end of that Sub Increment_SalaryArrears_Save() you will find Priyantha’s attempt.

WkBk 2024-04-28 xlsm Module Save Sub Increment_SalaryArrears_Save().JPG

I will take a guess that maybe your formula is doing what Priyantha wants, but it’s always a bit difficult to know. It looks reasonable:

PascalFormula.JPG

Similarly I am rather surprised that Han’s formula is doing what Priyantha wants, but once again, it’s always a bit difficult to know: It looks strange:

HansFormula.JPG

I think maybe when Priyantha says " That Code working" it may have some subtle meaning.

Alan

P.S. 1 Pascal, your code is working because the worksheet, "ArrearsShedule" , is most likely active. But I will take a guess that you may have meant to add another couple of dots ,
.Range(.Cells(lr + 1, "G"), .Cells(lr + 1, lastcol))

Edit P.S. 2
Using Pascal's coding , this would do the same

Code: Select all

With wb.Worksheets("ArrearsShedule")
  lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
  startrow = 3
  lr = 2
  Do While .Cells(lr + 1, 2).Value <> ""
    lr = lr + 1
  Loop
'  .Range(.Cells(lr + 1, "G"), .Cells(lr + 1, lastCol)).FormulaR1C1 = "=SUM(R" & startrow & "C:R" & lr & "C)"
  .Range(.Cells(lr + 1, "G"), .Cells(lr + 1, lastCol)) = "=SUM(G" & startrow & ":G" & lr & ")"
End With

- a relative referrence is a relative referrence by any notation ( referrence link later when I find it ) It’s a subtle point:
You effectively tell Excel to refer to the column where the formula is by using C which is actually more explicitly written as C[0]
I effectively tell Excel to refer to the column where the formula is by omitting the $ before the G
( I am mentioning that for others generally passing, I expect you Pascal and Hans know that better than me )

[p45cal]

It’s in the workbook 2024-04-28.xlsm

That explains it; I was reluctant to go hunting for it.

I think maybe when Priyantha says " That Code working" it may have some subtle meaning.

P.S. 1 Pascal, your code is working because the worksheet, "ArrearsShedule" , is most likely active. But I will take a guess that you may have meant to add another couple of dots

Quite true! Although I've generally found that the . before Range is not needed, the dots within the parentheses of that expression being sufficient.

[DocAElstein]

I was reluctant to go hunting for it.

Good decision – I was confused for a while as to why there where 2 Workbooks. I think for the purposes of this particular problem, workbook 2024-04-28.xlsm just has the coding in it, but we are only interested in doing anything in workbook Arrears Shedule.xlsm for this particular issue.
_.______________________

About the Range dot stuff …,
It's one of the few small areas of the massive VBA subject that I understands quite well, but nevertheless hopefully someone smarter will pop by and confirm, correct or add to this ….

If, in that code line of yours you ever found , as generally you did ….. the dots within the parentheses of that expression being sufficient. ….
, then I think you would, in those situations, have also have found that you could have done away with all of the 3 dots

It all depends where your code is.
Worksheets object code module…..
If you are in a worksheets object code module then Cells or Range will refer to that worksheet. In that situation, you are using the Worksheets Range property. You are relying on the implicit default thingy, and Excel will effectively add the Worksheets("xyz"). or Me. bit or effectively something similar, in front of all three of them
Normal code module…..
If you are in a normal code module, then Range will refer to the Application Range property and Excel will effectively add like Application. in front of all of them. Application Range itself relies on the string in the (" ") bit. If you just give the short cell address bit, as most everyone always does, then Excel will effectively add the name of the active worksheet. You could think of it as adding an ActiveSheet.Name bit and a ! bit in, like in the form , ActiveSheet.Name & "! , something like,
Application.Range("" & ActiveSheet.Name & "!A1") =
I think it’s a similar situation with Cells in a Normal code module. (But a while ago I learnt here that ActiveCell returns a Range object of the Selection if it can, and both are properties of the Window object. So that makes me a bit unsure about where Cells is looked for if you are not explicit with it. But I think whatever it’s up to, it effectively it ends up looking at , like Range does , on the thing you have active, for the case of your coding in a Normal code module.
ThisWorkbook code module…..
I think the situation is the same as when in a Normal code module, but I never did that much, and it’s just a guess.



So one thing that all boils down to is, … do like
With …….. worksheet you are interested in
Range(.Cells(lr + 1, "G"), .Cells(lr + 1, lastcol))
End With
(missing the first dot before the Range), if either the worksheet you are interested in is not active, or, if your coding is in a worksheets object code module which is not of the worksheet you are interested in, … then you will be in trouble. If those two situations do not occur, then you could miss out all the dots. That would be frowned on I expect, professionally, missing the dots. I probably wouldn’t do it, missing the dots, ( Although I ‘aint a professional in computing, so more generally I say, just IMHO, do anything you wanna do ) )




Ref
Excel Matters Referring to Ranges in VBA

[snb]

Use a dynamic table; Totals row is a builtin option.

[p45cal]

Agreed. This snippet would throw an error if the code was in a sheet code-module that wasn't sheet ArrearsShedule's code-module.
It would run, giving the desired similar addresses when it was:
in any standard code-module of any (of 3) workbook(s) (same instance of Excel)
in any workbook code-module
regardless of which workbook was the active workbook and which sheet was the active sheet from among those (3) workbooks.

The snippet:

Code: Select all

Set wb = Workbooks("Arrears Shedule.xlsm")
With wb.Worksheets("ArrearsShedule")
  Set Rng1 = .Range(.Cells(3, "G"), .Cells(3, 9))
  Set Rng2 = Range(.Cells(3, "G"), .Cells(3, 9)) 'missing dot.
  MsgBox Rng1.Address(0, 0, external:=True) & vbLf & Rng2.Address(0, 0, external:=True)
End With

[DocAElstein]

That is a very good example, - as I said … someone smarter will pop by … and … add to this
That example belongs in any blog, IMO. Initially I did not get it. What I mean is, I initially figured that Rng2 would error if worksheet, "ArrearsShedule" was not active. But as you say, it’s OK.

Let me see if I can explain why it works OK
It’s a variation perhaps of what I said …… Application Range itself relies on the string in the (" ") bit …. Perhaps I should have said, Application Range itself relies on the bit in the ( ). If that is fully explicit, as it is in that case, then all is OK
( In fact, this has jogged my memory… I remember now having had a very similar example puzzling me a long, long time ago. Someone thought to be just about the smartest in this area of VBA, said it was probably to do with Application Range working a bit pseudo Late Binding’ish. Maybe at a stretch of the imagination it is – maybe you could say it finds out where it’s supposed to be going later rather than sooner. The answer of where is in the ( )
A worksheet knows which worksheet it is, pretty quickly, I guess …. )
( The statements like … Application Range itself relies on the string in the (" ") bit , or now , Application Range itself relies on the bit in the ( ) …. – I made them up myself as I went along …..
_.____________

Also ….. … someone … will pop by … and … correct
Let me correct myself: This statement of mine was not quite right …. If, in that code line of yours you ever found , as generally you did ….. the dots within the parentheses of that expression being sufficient. ….
, then I think you would, in those situations, have also have found that you could have done away with all of the 3 dots

In my version of your snippet below, Rem 3 ( with all dots removed ) it will not always work the same as Rem 2.


Once again, thanks to someone smarter chipping in, I am getting closer to the ultimate correct understanding… when I get there, I will share the ultimate blog, Lol , ( and give the blame appropriately to those that set me straight , :) )

Code: Select all

Sub test() ' https://eileenslounge.com/viewtopic.php?p=316868#p316868
Set wb = Workbooks("Arrears Shedule.xlsm")
    With wb.Worksheets("ArrearsShedule")
    
    Rem 1 'This will work and give the correct result anywhere
     Set Rng1 = .Range(.Cells(3, "G"), .Cells(3, 9))
    MsgBox Rng1.Address(0, 0, external:=True)
     
    
    Rem 2 ' This will not work in any worksheet object code module other than that of "ArrearsShedule"
'   , but it will work in any normal code module regardless of what worksheet is active, which initially i had not twigged to
     Set Rng2 = Range(.Cells(3, "G"), .Cells(3, 9)) 'missing dot.
    MsgBox Rng2.Address(0, 0, external:=True)
    
    
    Rem 3 ' This will never error. But it won't always give the same results as Rem 2. It will give the address of the ActiveSheet if its placed in a normal code module, or the address of the worksheet in a worksheets object code module
     Set Rng3 = Range(Cells(3, "G"), Cells(3, 9)) 'missing all 3 dots.
    MsgBox Rng3.Address(0, 0, external:=True)
    
    End With

End Sub

Edit - I just did edit and added the snippet I forgot

[Priyantha]

Dear All,
I Edited Hans code _ wb.Worksheets("ArrearsShedule").Cells(5, col).FormulaR1C1 = "=SUM(R" & startrow & "C" & col & ":R" & col & "C" & lr & ")"

as below

wb.Worksheets("ArrearsShedule").Cells(5, col).FormulaR1C1 = "=SUM(R" & startrow & "C" & col & ":R" & lr + 1 & "C" & col & ")"
but a have missed to imform the forum. I am very sorry & I thanks all of you.

Priyantha

[DocAElstein]

as below
wb.Worksheets("ArrearsShedule").Cells(5, col).FormulaR1C1 = "=SUM(R" & startrow & "C" & col & ":R" & lr + 1 & "C" & col & ")"

Yes, work do also and give corrcet results

So we have three alternatives, all work do also and give correct results

_ 1 You changing Hans code line in your original attempt

Code: Select all

    lastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    startrow = 3
    lr = 2
    
    Do While wb.Worksheets("ArrearsShedule").Cells(lr + 1, 2).Value <> ""
    lr = lr + 1
    Loop

        For col = 7 To lastCol    
        wb.Worksheets("ArrearsShedule").Cells(5, col).FormulaR1C1 = "=SUM(R" & startrow & "C" & col & ":R" & lr + 1 & "C" & col & ")"
        Next col

Note: You are using effectively fixed, rather than relative, referrences, hence you must loop all columns


or
_2 Pascal ( p45 ) alternative coding that does away with looping columns - does all column formulas at once, - since the applied formula has relative column referrences !! - I expect you missed that?, ( as you likely misssed most everything else, Lol! )

Code: Select all

With wb.Worksheets("ArrearsShedule")
  lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
  startrow = 3
  lr = 2
  Do While .Cells(lr + 1, 2).Value <> ""
    lr = lr + 1
  Loop
  .Range(.Cells(lr + 1, "G"), .Cells(lr + 1, lastCol)).FormulaR1C1 = "=SUM(R" & startrow & "C:R" & lr & "C)"

End With

or
_3 my slightly modified version of Pascal's

Code: Select all

With wb.Worksheets("ArrearsShedule")
  lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
  startrow = 3
  lr = 2
  Do While .Cells(lr + 1, 2).Value <> ""
    lr = lr + 1
  Loop
  .Range(.Cells(lr + 1, "G"), .Cells(lr + 1, lastCol)) = "=SUM(G" & startrow & ":G" & lr & ")"
End With

Alan

[Priyantha]

Thanks Again