Autofill Column with Formula

[Simonc64]

Hi Guys

Hopefully a very simple VBA solution to this.

I have a vlookup formula in say cell "F2" that is dependent upon a value appearing in cell "E2".


What I am after is some VBA code that, when a value appears in cell "E3", the vlookup formula in cell "F2" 'copies' itself to cell "E3" and so on, maybe all the way to the end of the column.

Simple as that !!

I am aware that i could just copy "F2" down say 20k rows, but im more concerned with understanding the VBA coding

Thanks in advance

 

[DanteAmor]

Put in the following code in the events of your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E:E")) Is Nothing Then
    If Target.Row < 3 Then Exit Sub
    Dim c As Range
    For Each c In Target
      Range("F2").Copy c.Offset(, 1)
    Next
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[Simonc64]

Put in the following code in the events of your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E:E")) Is Nothing Then
    If Target.Row < 3 Then Exit Sub
    Dim c As Range
    For Each c In Target
      Range("F2").Copy c.Offset(, 1)
    Next
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Hi Dante

Thanks for this; it seems to work to a point; it fills out column F but also overwrites the formulae in the next 3 columns as well?

 

[DanteAmor]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E:E")) Is Nothing Then
    If Target.Row < 3 Then Exit Sub
    Dim c As Range
    For Each c In Target
      If c.Column = Columns("E").Column Then
        Range("F2").Copy c.Offset(, 1)
      End If
    Next
  End If
End Sub

 

[Simonc64]

Thank you - seems to have fixed it !

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.