help with my macro

[sasoderl]

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?

 

[Zack Barresse]

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]

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]

Why not use an Input Box in the fill code to request the numbers of rows?

Smitty

 

[sasoderl]

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]

No problem. How's this:

    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

 

[sasoderl]

thanks for your help smitty