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

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

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.

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



--
Regards
Dave Hawley
www.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.

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

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.