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 15 Apr 2010 08:05
On Apr 14, 11:05 pm, "ozgrid.com" <d...(a)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" <rick.mal...(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.

First  |  Prev  | 
Pages: 1 2
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?