If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filldown starting from F2 instead of F1
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 |
#2
|
|||
|
|||
Filldown starting from F2 instead of F1
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 |
#3
|
|||
|
|||
Filldown starting from F2 instead of F1
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. |
#4
|
|||
|
|||
Filldown starting from F2 instead of F1
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. |
#5
|
|||
|
|||
Filldown starting from F2 instead of F1
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 |
#6
|
|||
|
|||
Filldown starting from F2 instead of F1
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. |
Thread Tools | |
Display Modes | |
|
|