On Apr 14, 11:05*pm, "ozgrid.com" wrote:
No need for FillDown;

Sub Auto_Fill()
* Dim lRow As Long
* Dim actcol As Long

* * actcol = ActiveCell.Column

* * * * With ActiveSheet
* * * * * * lRow = .Range("F" & .Rows.Count).End(xlUp).Row
* * * * * * .Range("F1:F" & lRow).FormulaR1C1 = _
* * * * * * "=IF(R[1]C2,R[1]C[" & actcol - 6 & "],"""")"
* * * * End With
* End Sub

Right back to the original problem/question. This is still off by one
cell. i.e. The formulas in Col F are all up one cell too high and when
this macro is ran it replaces the label in cell F1 with the formula in
F2. Is there any way to avoid this?
Thanks again,
Ephraim
--
Regards
Dave Hawleywww.ozgrid.com"Ephraim" wrote in message

...
On Apr 14, 7:06 pm, Dave Peterson wrote:



Try changing all those F1 in the code (both of them!) to F2 and see what
happens.

Ephraim wrote:

Can anyone help me modify the following so that when Col F fills down
it starts at F2 instead of F1. It works great but everything in Col F
is up one cell too high. It also replaces the text in F1 which I would
like to have unchanged.

Thanks Mr Dibben for your original reply to get me this far a couple
of weeks ago.

Thanks
Ephraim

To change formulas in F to activecell column reference.

Sub Auto_Fill()
Dim lRow As Long
Dim actcol As Long
actcol = ActiveCell.Column
With ActiveSheet
.Range("F1").FormulaR1C1 = "=IF(R[1]C2,R[1]C[" & _
actcol - 6 & "],"""")"
lRow = .Range("F" & Rows.Count).End(xlUp).Row
.Range("F1:F" & lRow).FillDown
End With
End Sub

Gord Dibben MS Excel MVP

--

Dave Peterson

Thanks but I've already tried all of those changes. Nothing I changed
worked.