help with my macro

sasoderl

Board Regular
Joined
Feb 17, 2004
Messages
82
I used vlookup and then started recording a macro. I used the fill handle to record the macro. What happens when I want to run the same macro on a different file that has more or less rows than the original that I recorded the macro on?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
you can either change the macro manually, every file it's added to, or do some fancy dynamic stuff. latter of which you'd probably get some answers for around here is you posted some data and your current recording.
 

sasoderl

Board Regular
Joined
Feb 17, 2004
Messages
82
changing the macro manually every file it is added to is not an option. SO, here is an example

I have an excel file called DATA.xls that contains the lookup data. it is contained in cells A1:D57 and that entire range has been named "data"

the file that i created the macro on had around 300 rows. i used

B1 = VLOOKUP(A1, data, 2, false) and then the fill handle all of the way down... then...

C1 = VLOOKUP(A1, data, 3, false) and then the fill handle all of the way down... then...

D1 = VLOOKUP(A1, data, 4, false) and then the fill handle all of the way down...

Now, if I have a new excel file with say 400 rows and run the macro, it only fills in the info for the first 300 rows. I would like to change the VB to a dynamic number rather than a static number
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Why not use an Input Box in the fill code to request the numbers of rows?

Smitty
 

sasoderl

Board Regular
Joined
Feb 17, 2004
Messages
82

ADVERTISEMENT

unfortunately, i am extremely new at this. i have a degree in computer science tho and should be able to understand however, can you please elaborate. i need to make this macro as easy as possible for novice users
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
No problem. How's this:
Code:
    Range("A1:D1").Autofill Destination:=Range("A1:D" & _
        InputBox("Please Enter the Number of Rows to Fill Down", _
    "Autofill Range")), Type:=xlFillDefault
When ptrompted enter the number of rows that you want to fill down.

Hope that helps,

Smitty
 

Watch MrExcel Video

Forum statistics

Threads
1,127,846
Messages
5,627,235
Members
416,232
Latest member
Ash1432

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