 # Autofilling

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

Ed
[SIZE=6]*ficus*[/SIZE]
Thanks to all for making this website work!

• 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``````
• 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:C,2,FALSE)"``````

Ed
[SIZE=6]*ficus*[/SIZE]
Thanks to all for making this website work!

• 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:

B_O_B

• Re: Autofilling

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

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Autofilling

Try this:

Code
``````Range("D1", Cells(Cells(Rows.Count, "A").End(xlUp).Row, "D")).FormulaR1C1 = _
"=VLOOKUP(RC[-1],C:C,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:C,2,FALSE)"``````

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!