Adding formulae to excel workbooks.. with a twist

[lovi]

Hello all,

Im writing a macro that will open every xls files in a folder and add an equation at a specific cell in each of them. An example of the algorithm can be seen below.

My problem is that the range which will need to be autofilled will vary (the number of rows will vary for each workbook). How can I modify this algorithm so that it can determine the range it needs to fill before filling it?

Any help/suggestions will be deeply appreciated

Lovi


Option Explicit
Sub Open_All_Files()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String


Dim ru As Double
sPath = "C:\New folder" 'location of files
ChDir sPath
sFil = Dir("*.xls")
Do While sFil <> ""
Set oWbk = Workbooks.Open(sPath & "\" & sFil)
Range("E1").Select
ActiveCell.FormulaR1C1 = "ffff"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=(RC[-4]+RC[-3])*RC[-1]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E5")


oWbk.Close True
sFil = Dir
Loop
End Sub

 

[Jonmo1]

Perhaps

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 Double
sPath = "C:\New folder" 'location of files
ChDir sPath
sFil = Dir("*.xls")
Do While sFil <> "" 
    Set oWbk = Workbooks.Open(sPath & "\" & sFil) 
    lr = Cells(Rows.Count, "A").End(xlup).Row
    Range("E1").FormulaR1C1 = "ffff"
    Range("E2:E" & lr).FormulaR1C1 = "=(RC[-4]+RC[-3])*RC[-1]"
    oWbk.Close True 
sFil = Dir
Loop
End Sub

 

[lovi]

Thank you jonmo1, this works!
Just wondering whether you know what can be done if the table does not start at A1 but starts at say E4?

 

[Jonmo1]

Thank you jonmo1, this works!
Just wondering whether you know what can be done if the table does not start at A1 but starts at say E4?

You must first ask yourself...

"How do I know whether the table starts in A1 or E4?"

What do you look for, a certain header?
Are the cells ALL blank to the left/right of the table?
Is it the Top/Left Most populated cell?

 

[lovi]

jonmo1, I have tried different positions for the table(changing the cell reference in the code) and it works. Earlier I was thinking that the ' lr = Cells(Rows.Count, "H").End(xlUp).Row' would be affected by blank cells etc, but 'End(xlup)' finds the last value in the column

Once again, thanks a lot, in about a week I will have 100+ spreadsheets to which I need to add 12 formulae. your code will save me a lot of time

Lovi