[EXCEL VBA] Help - Datafilling 1 wks from another (need a fast method)

I've got a massive Excel VBA 'macro' that in the middle of running ends up with two worksheets of data. These two sheets are farily large, containing up to 5,000 rows of data on the largest.

'-------made up example-------------
sheet 1 contains the following column names:
Car Name, Car Make, Car Model, Car Color, Car Size, OWNER

sheet 2 contains only one matching column:
OWNER, address, phone number, sex, age, height weight.

note: Sheet 2 will have all unique records, but an OWNER name may show up 50 times.
'-----------------------------------------

'------What I have to do -------------
What I have to down is:
A) Insert some columns into sheet 2 (for every column except "OWNER" that exists in Sheet 1)
B) datafill those extra columns with the data from sheet 1
'--------------------------------------------

'------How I'm doing it now -------------
A) I create some arrays and dim then by the number of rows in Sheet 1 (minus 1 to account for the column headers on row1).
B) I load those arrays with the data from Sheet 1
C) I go down ever row in Sheet 2 and if Owner matches I dump the array data in to the appropriate cells.
'------------------------------------------------

'----How I do it now -------------
'--note: made up on the fly
'--note: The real routine has about 20 'arrays' for all the columns

Code:

---

Redim MyArray1(LastRow - 1)
Redim MyArray2(LastRow -2)

Sheet1.activate

For X = 2 to LastRow
        MyArray1(X-1) = Cells(X,1).value
        MyArray2(X-1) = Cells(X,2).value
Next X

Sheet2.activate

For X = 2 to LastRow
        For Y = 1 to Sheet1.LastRow - 1
                If Cells(X,3).value = MyArray1(Y) Then
                        Cells(X,4).value = MyArray2(Y)
                        Exit For
                End If
        Next Y
Next X

---

'-----------------------------------------------------------------------------


'-------What I'm looking for ---------------------
That method is so freaking slow it makes me cry.

I barley know jack about Excel forumlas and even less about how to use them from VBA.

With the forumlas or some other method, what is the fastest way that I can datafill sheet2 with the data from sheet1?
'--------------------------------------------------------


THANKS

Hi,

You want to look into the excel function VLookup (check the help files).

It will only work if OWNER is unique in the look up list (sheet 2)

How to make it work:
1) Manually (so you can check whether it works ok)
2) VBA Code entered and then copied and pasted to make it a value



1)
- Open both sheets
- On sheet 1, after the OWNER column, put "Vlookup(<cell reference to OWNER>,"
- Now it gets a little tricky, switch to sheet 2, select the top left most cell of data in the owner column. Select all the data to the right and down to use a look up table/array
- If there are no $'s in the address (look at the top formula bar) then press F4, then enter ",2,false)"
without the speechmarks
- Copy and paste the formula down as far as you need.
- Highlight the formula cells, copy, right mouse select paste special and choose values

That should leave you with values for the first column from the second sheet on the first sheet. heheh.
If you are happy using the Vlookup function, you can copy n paste the function into the remaining missing columns of the first sheet and change the reference cell, and the number of the column to return (which we entered as 2 above).

2)
As above but put in via code, and which ever line you are on as the first cell reference. Then the code can fill it up for you as you go get a coffee. Then it can also copy n past special for you too so when you get back, its all ready to use.... :)


Its only a quick how to, so read up on the help file n post up if you are still stuck.


Vince

OK, that's cool... I'll give that a try at work today. The only problem would be that Sheet1 is not a static size. So I guess I would need to make the VLookup up on the fly and insert it into the cells. But, I guess after the first one is inserted, I could just autofill down in code.


I'd need to get rid of the forumlas right after though. Is there a fast way to do something like:

For X = 1 to LastRow
Cells(X,3).text = Cells(X,3).value
Next X


... (fast way to do something like the above) to an entire column?



Maybe I could:
1) insert a new column
2) copy the old one and paste-special (values)
3) delete the original column


If I remember right, you can "Paste-Special-Values" ontop of a copy source.


Thanks!