Copying formulas down in VB

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
David

Try this.
Code:
Range("B1").Resize(Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-1]:C,2,0)"
 
Upvote 0
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
 
Upvote 0
David

Try this.
Code:
Range("B2").Resize(Range("A" & Rows.Count).End(xlUp).Row-1).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-1]:C,2,0)"
 
Upvote 0
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..

Code:
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. :)
 
Upvote 0

Forum statistics

Threads
1,217,257
Messages
6,135,502
Members
449,944
Latest member
parag385

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top