If the original code worked ok, then the revised code should work ok.

My bet is that the original code didn't do what you wanted with your current
data.

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

This relies on column F to find the lastrow to fill. If column F is empty, then
you won't get the correct range filled in.

I like to use a different column that's always used when that row is used (name,
id, date, some sort of field that's always used).

If your data is like that, you can modify your code to use that column to find
the lastrow.


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

(I used column A -- you can change it to what you need.)





Ephraim wrote:

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.

--

Dave Peterson