Hi,
I have a workbook that opens other workbooks by date and then concatenates a formula before dropping it into a cell and then autofilling across a range. Problem is, as more and more workbooks are added the formula is getting bigger. I heard somewhere that the longest formula you can have in a cell is 1024 characters. Is there any way to split a formula across a few cells and then total up the results? This is my code so far to make the formula:
Sub populate()
Application.ScreenUpdating = True
sheets("reports").Select
Dim MyCell As Range
Dim Wb As Workbook
Dim myformula As String
Set MyCell = ThisWorkbook.sheets("reports").Range("B2")
For Each Wb In Workbooks
If Wb.Name <> ThisWorkbook.Name Then
myformula = myformula & "'" & Wb.Name & "'" & "!R2C2" & ","
End If
Next Wb
MyCell.FormulaR1C1 = "=SUM(" & Left(myformula, Len(myformula) - 1) & ")"
MsgBox (Len(myformula))
End Sub
Any help is much appreciated.
Bookmarks