Filldown starting from F2 instead of F1

  • Thread starter Thread starter Ephraim
  • Start date Start date
E

Ephraim

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.
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
Click to expand...
 
Try changing all those F1 in the code (both of them!) to F2 and see what
happens.




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.
Click to expand...
Thanks Mr Dibben for your original reply to get me this far a couple
of weeks ago.

To change formulas in F to activecell column reference.
Click to expand...
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
Click to expand...
Gord Dibben  MS Excel MVP
Click to expand...
Click to expand...

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
Try changing all those F1 in the code (both of them!) to F2 and see what
happens.




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.
Click to expand...
Thanks Mr Dibben for your original reply to get me this far a couple
of weeks ago.

To change formulas in F to activecell column reference.
Click to expand...
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
Click to expand...
Gord Dibben MS Excel MVP
Click to expand...
Click to expand...

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.)




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




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.
Click to expand...
Thanks Mr Dibben for your original reply to get me this far a couple
of weeks ago.

To change formulas in F to activecell column reference.
Click to expand...
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
Click to expand...
Gord Dibben MS Excel MVP
Click to expand...
Click to expand...

Thanks but I've already tried all of those changes. Nothing I changed
worked.
Click to expand...
 
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
Click to expand...
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

Try changing all those F1 in the code (both of them!) to F2 and see what
happens.
Click to expand...
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
Click to expand...
--
Click to expand...

Dave Peterson
Click to expand...

Thanks but I've already tried all of those changes. Nothing I changed
worked.
Click to expand...
 
Back
Top