Autofill Based on a Changing Column

[sgltaylor]

Hi

Any assistance with the following would be most welcome.

I am trying to find some VBA code which will allow me to copy a
formula from an ActiveCell down to the last row in the ActiveCell
column based on the non blank cells in the column to the left of the
ActiveCell column.

For example, if the ActiveCell is equal to C5 and the range B5 to B20
contains entries.
I would like to autofill the formula from cell C5 down to C20.

To further complicate matters, the ActiveCell may be in column C in
one instance and in another it could be column D etc (in other words,
it can be in any column depending on the source data). The column to
the immediate left will always have entries in it but the number of
rows will change as well.

Thanks,

Steve

PS I am using Microsoft Office 2003 with windows XP

 

[OssieMac]

Hi Steve,

Hope I have interpreted your question correctly. Ensure that you back up
your data first just in case.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Sub CopyPasteFormula()

Dim rngActiveCell As Range
Dim lngRow As Long

Set rngActiveCell = ActiveCell

lngRow = rngActiveCell.Offset(0, -1) _
.End(xlDown).Row

rngActiveCell.Copy _
Destination:=Range(rngActiveCell, _
Cells(lngRow, rngActiveCell.Column))

End Sub

 

[sgltaylor]

Hi Steve,

Hope I have interpreted your question correctly. Ensure that you back up
your data first just in case.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Sub CopyPasteFormula()

Dim rngActiveCell As Range
Dim lngRow As Long

Set rngActiveCell = ActiveCell

lngRow = rngActiveCell.Offset(0, -1) _
  .End(xlDown).Row

rngActiveCell.Copy _
  Destination:=Range(rngActiveCell, _
  Cells(lngRow, rngActiveCell.Column))

End Sub

A big thank you.
The code worked great.

Cheers,

Steve