Copying formulas down in VB

[davidhall80]

First off, I'd like to say thank you to everyone who has been so helpful. i really am learning alot from this message board. Now on to my question.. The first part of my macro is....I have to do a vlookup in column B and I a referencing column A. My question is...I never know how long my data set will be in column A, so i drag my formula down to line B10000 in my macro to ensure that I never miss anything. is there a way that I could do a count of column A an then drag my formula down only as far as I need it to go?

 

[just_jon]

What is the formula you want to seed?

 

[davidhall80]

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 9/17/2006 by David Hall
'

'
Range("B1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-1]:C,2,0)"
Range("B1").Select
Selection.Copy
Range("B1:B10000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=-3
Range("A1").Select
End Sub

 

[Norie]

David

Try this.

Range("B1").Resize(Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-1]:C,2,0)"

 

[davidhall80]

worked like a charm.....thanks alot

 

[davidhall80]

One more question......I'm actually copying down from B2 instead of B1. The first row is my column header row. Can it work from B2. I tried to replace B1 with B2 in the code above, but since its taking the entire count of Row A, it copies down 1 extra cell. Is this possible

 

[Norie]

David

Try this.

Range("B2").Resize(Range("A" & Rows.Count).End(xlUp).Row-1).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-1]:C,2,0)"

 

[Zack Barresse]

Why even use VBA?? If you enter this formula in B2 and, barring that you do not have gaps in your data in column A (this is always indicative of the columns data directly to the left) you can position your cursor over the bottom right corner of the cell (cursor should turn into a bold + sign) and double click. This will AutoFill your formula down until it [Excel] sees a change in data (the last cell with data). VBA would just be cluttering this up (unless you had breaks in your data).

Also, there's no need really for the Resize either, or the R1C1 style. You could just use..

Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row).Formula = "=VLOOKUP(A2,Sheet2!A:B,2,0)"

It's pretty much six one, half-a-dozen the other, another way to skin this cat.

 

[davidhall80]

Cool...Thanks...they both worked. Appreciate your time guys