Macro for summation with a variable

[lovi]

Hello all, the algorithm below is meant to open every xls file in a folder then calculate a sum but each workbook have a different number of rows. Im trying to write the sum command with a variable 'ru' to accomodate for this but no success so far. Here's the macro I have, can someone have a quick look and point out my error please?

Any help is really appreciated

Lovi

Option Explicit
Sub Open_All_Files()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
Dim lr As Long
Dim ru As Integer
sPath = "C:\New folder"
ChDir sPath
sFil = Dir("*.xls")
Do While sFil <> ""
    Set oWbk = Workbooks.Open(sPath & "\" & sFil)
    lr = Cells(Rows.Count, "H").End(xlUp).Row
    ru = lr - 5
    ActiveSheet.Range("N5").Value = ru
    ActiveSheet.Range("N6") = "=SUM(R[5]C[-2]:R[ru]C[-2])"
    
    oWbk.Close True
sFil = Dir
Loop
End Sub

 

[Rijnsent]

Hi Lovi,
your main problem is:
ActiveSheet.Range("N6") = "=SUM(R[5]C[-2]:R[ru]C[-2])"
I guess that has to be:
ActiveSheet.Range("N6").formulaR1C1 = "=SUM(R[5]C[-2]:R[ru]C[-2])"

Secondly, that formula means:
R[5]C[-2] = L11 -> 5 cells down and 2 columns left from the cell where you put the formula.
Say, the last found row (lr) is 100, then ru = 95
R[ru]C[-2] = L101

I guess with that logic you should be able to repair that line (as I don't know what you want to add up in cell N6).

Cheers,
Koen

 

[Peter_SSs]

Since you are using a variable in your formula string, you would need to structure it like this

"=SUM(R[5]C[-2]:R[" & ru & "]C[-2])"

If that does not solve your problem, please try to explain in words, just what you are trying to sum on each sheet.