Autofilling

Foxxy1 · Jul 9th 2004

Is there a way to fill column "B" down to the very last cell located in column "A" when column a varies each time? Thanks for any help.

Jong · Jul 9th 2004

Of course there is a way!

Here is some code that may point you in the right direction:

Code

Dim RowPtr as Integer
RowPtr = 1


While Worksheets("MySheet").Range("A" & RowPtr) <> ""
    Worksheets("MySheet").Range("B" & RowPtr) = "FILL"
    RowPtr = RowPtr + 1
Wend

Foxxy1 · Jul 9th 2004

Thanks, but...

I'm trying to get it to work and I kind'a get what's happening, but I'm trying to get a formula to copy downward and I'm just not grasping the concept. This is the formula I'm working with:

Code

Range("D1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],C[5]:C[10],2,FALSE)"

Please Help. Thanks again

BeginnerBob · Jul 10th 2004

Now then Foxxy,

There are always different ways around any problem. However, for autofilling formula downwards I normally use this solution.

''Calculate the end cell of column A

Code

a = Range("A65536").End(xlUp).Row


Range("B2").Select
ActiveCell.FormulaR1C1 = "Type in your recorded macro formula"
Range("B2").Select
Selection.autofill Destination:=Range("B2:B" & a)

If you are using a lookup function, perhaps after the formula, when all the rows had calculated, then you should copy and paste special the values - just an idea if you are dealing with lots of data.

Cheers.

BOB :guitar:

shg · Jul 5th 2007

Re: Autofilling

Foxxy1, BeginnerBob, I just added code tags to your last posts.

Cardinal rule of the forum, guys, please be mindful.

shg · Jul 5th 2007

Re: Autofilling

Try this:

Code

Range("D1", Cells(Cells(Rows.Count, "A").End(xlUp).Row, "D")).FormulaR1C1 = _
    "=VLOOKUP(RC[-1],C[5]:C[10],2,FALSE)"

Or this may be clearer:

Code

lngRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("D1", "D" & lngRow).FormulaR1C1 = "=VLOOKUP(RC[-1],C[5]:C[10],2,FALSE)"

Autofilling

Participate now!

Share