Insert Formula using VBA

[dannyok90]

Hi All,

I'm wondering if its possible to insert a formula into a cell based on other cells.


In columns A, B and C i have a formula dragged down to row 10000 that gets information from a data set that starts in D1. It's taking up alot of memory and causing the spreadsheet to calculate slowly


I paste data in D1 and the data grows week on week so i just keep pasting it over d1. I was thinking I could use vba to insert a formula into A B & C that would populate as the data set grows, that way there would only be formulas in the rows upto the end of the data set. so the spreadsheet wouldn't be so slow?


Help appreciated

Dan

 

[Norie]

Dan

Do you mean that whenever data is entered in column D the formulas in columns A, B & C are copied down?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 4 Then
        Application.EnableEvents = False
        Intersect(Range("A1:C1").EntireColumn, Rows(Target.Row - 1)).Copy Target.Offset(, -3).Resize(Target.Rows.Count)
        Application.EnableEvents = True
    End If

End Sub