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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

davidhall80

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
David

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

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663

ADVERTISEMENT

worked like a charm.....thanks alot
 

davidhall80

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)"
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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. :)
 

Forum statistics

Threads
1,141,681
Messages
5,707,791
Members
421,527
Latest member
Tamiwsw

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
Top