Filldown starting from F2 instead of F1
in [Functions]
|
Prev: How can I add the values based the color of font and the date
Next: how do I import data in excel form SQL server?
From: Ephraim on 14 Apr 2010 15:44 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
From: Dave Peterson on 14 Apr 2010 19:06 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
From: Ephraim on 14 Apr 2010 22:18 On Apr 14, 7:06 pm, Dave Peterson <peter...(a)verizonXSPAM.net> 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.
From: ozgrid.com on 14 Apr 2010 23:05 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" <rick.malone(a)gmail.com> wrote in message news:9c439f58-d7f2-4d0f-a94b-b11319199abb(a)r1g2000yqb.googlegroups.com... On Apr 14, 7:06 pm, Dave Peterson <peter...(a)verizonXSPAM.net> 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.
From: Dave Peterson on 15 Apr 2010 08:02
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 <peter...(a)verizonXSPAM.net> 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 |