Vlookup in VBA to Loop with clear definitions

[mtilbury]

I have a large Macro and want to incorporate a VBA lookup, so I can remove the formula from my sheets at it is very slow running and saving.

I know there are several ways of doing this, but I am looking for an easy code to adapt as I have 10 lookup columns

The code needs to work down a list of 60,000 records. - All the codes I am finding online are for one specific cell lookup and not run down the entire column.

Ideally I want to define the Columns and worksheets to better understand the code.

The two Worksheets are 'Main' and 'Lookup'

Example Set Dim imain, ilookup as worksheet
then Set imain = Sheet ("Main")

I'm looking to go down the Application.WorksheetFunction.Vlookup route

But want to Specify the column ranges if possible. Either in the VB code. So Status = Range ("A:B")

or Highlighting the whole range in Excel and defining the group as Status. The code could then refer to 'Status'

[TMS]

Are you saying that you want to be able to loop through 60,000 rows and do a VLookup for each one? Can't see how that is better than having a VLOOKUP on the worksheet. When and how would you plan to run the macro?

You would probably be better with a Worksheet Change event handler monitoring the data/input cells and dropping a VLOOKUP formula into the relevant cell(s) for the Target row(s) and converting the formula(e) to values.


Regards, TMS

[mtilbury]

Yes, Each month I copy several spreadsheets into one which produces around 60,000 rows.
I then have 10 Columns that provide more details to each row based on a lookup table.
So 10 x 60,000 is 600,000 cells with a formula in them.

As you can imaging, processing and saving the file is very slow. If I use a VBA code the formula is not stored in 'real-time' So will make the file smaller and processing quicker.

Very interesting in your second comment. - Would that work with what i have described above?
I have never used a Worksheet change before.

[TMS]

Would that work with what i have described above?

Possibly. Let's say, for the sake of argument you have 5 worksheets each with 12,000 rows and, for our purposes, data in column A. If you monitor column A in the consolidated worksheet, and if you copy and paste 12,000 cells into column A, that would fire the Change event.

The change event could then populate the equivalent cells in whichever column(s) with a formula and then convert the formulae to values.

Something like:

Please Login or Register  to view this content.

Using Target.Row will cause the row number to auto adjust.

	A	B	C	D	E	F	G	H	I	J	K
1	a	1								1	=VLOOKUP($A1,Sheet9!$A$1:$B$10,2,FALSE)
2	b									2
3	c									3
4	d									4
5	e									5
6	f									6
7	g									7
8	h									8
9	i									9
10	j									10
11
12	a									1	=VLOOKUP($A12,Sheet9!$A$1:$B$10,2,FALSE)
13	b									2
14	c									3
15	d									4
16	e									5
17	f									6
18	g									7
19	h									8
20	i									9
21	j									10
22
23	a									1	(converted to values)
24	b									2
25	c									3
26	d									4
27	e									5
28	f									6
29	g									7
30	h									8
31	i									9
32	j									10

Sheet9:

	A	B
1	a	1
2	b	2
3	c	3
4	d	4
5	e	5
6	f	6
7	g	7
8	h	8
9	i	9
10	j	10

So, yes, I'd say it could work.

Regards, TMS

[Philb1]

I had a similar problem to you with things taking to long. First off I had 2520 Sumproduct formulas that were referencing whole columns. I nearly died of old age waiting for them to calculate every time I did something. I changed all of them to Countifs & while still referencing whole columns, improved things a lot, but still not fast enough for me. I put the formula below in a spare cell ie. Z1, to find the last used row.

Please Login or Register  to view this content.

Then used Indirect to reference to the resulting number in that cell. This restricted the Countifs arrays to the used range on the worksheet & stopped 2520 formulas from referencing a million rows with each formula array reference.
Something like the below maybe

Please Login or Register  to view this content.

I did try VBA as well & for that to have a chance of being comparable to using worksheet formulas in speed, will have to be Array VBA. I used 3 simultaneous arrays to get what I wanted & found that the formulas were just that little bit quicker

Hope thats of some help

[TMS]

INDIRECT is a Volatile function so it will cause excessive calculations. You would be better using Dynamic Named Ranges using INDEX to limit the ranges.

Prior to Excel 2007, you could not specify whole ranges for SUMPRODUCT. Even though you can now, it is not recommended.

I believe that functions like SUMIF, SUMIFS, COUNTIF, COUNTIFS, etc., are optimised to use the UsedRange and hence you may have introduced a performance hit by using INDIRECT. All hypothetical, of course.

[Philb1]

I'll look at that as well thanks. All I know is that there was a huge speed improvement when I Used Indirect. Be interesting to see if I get another boost now

Are you working night shift or do you have insomnia lol. Almost 1am there, I'd be out for the count

[mtilbury]

This appears to be working great on testing with dummy data. How can I move the results from Column J? I want to move this to Column C. I tried changing the target.offset to 0,4 But it still gives the result in J?

[mtilbury]

Hi TMS

I have this working now, However It is slowing down the rest of the Macro? When I copy over the data it gets copied Column by Column because each Source spreadsheet is different. I think with your coding it is running the sequence each time a column is pasted so it is running dozens of times.

If this is true is there a way of amending your formula to run on a command click?

Thanks in advance

[TMS]

slowing down the rest of the Macro?

What macro? What does it do? What is your process for importing data?

The way the macro is coded, it will monitor changes to column A. Any other changes are excluded by the first line of code

Please Login or Register  to view this content.

If a change is made to column A, then the cells receive the necessary formula and convert it to values. So, let's say that you import 10,000 rows/cells. The code will put the formulae in 10,000 cells and convert it to values. If you later needed to amend the odd cell, change a code, whatever, it would do just the same but for one cell.

However, we can add the classic "speed up" options to see if it makes a difference. In the code below, I've also added a boolean flag which will determine if the code is executed. That can be set to TRUE by clicking on a button. The code will automatically change it to FALSE when it has completed.

See if this helps:

Please Login or Register  to view this content.

[mtilbury]

Hi TMS
This is working great, a lot faster. One last issue, I have Multiple lookups for example cells A, C, E are copied over into the main spreadsheet. Columns B looks-up A, Columns D looks-up C, and Columns F looks-up E.

So as you can see I have multiple lookup sources that I would like to run at the same time. However looking at your code I am having an issue with this line.

Please Login or Register  to view this content.

This is looking up Column A. So it works for Column B to Lookup Column A. But Columns C,D,E,F do not work.

I tried entering '&', 'Or' but these does not work.

Will I have to define this for each Column. So copying the above code each time, but changing the target name.

Example
Target
TargetYear
TargetPostcode
....
....

I assume dropping the difference Lookups will be just pasting the following and amending the the correct fields. Its the Source I am struggling with.

Please Login or Register  to view this content.

[TMS]

Please Login or Register  to view this content.

Assuming it is basically the same formula, just using the previous column for the search, you could combine that logic into something like this:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Maybe hold fire on the last one. The principle is OK but it needs some refinement.

Will look later.

[mtilbury]

Ok Thank you.

All the formulas are different. So would need to enter a different formula for each one. Unfortunately I cant combine them as per your previous coding.

I would need to say look up this A Column in range Sheet9!$A$1:$B$10,2,FALSE), Then Column C would be Sheet9!$F$1:$G$10,2,FALSE)

Column E would be Sheet9!$I$1:$J$10,2,FALSE)

You see they each have a different lookup table. Basically Sheet9 has 3 tables. Each Column in Sheet 1 looks at one of the 3 tables.

---------------
I have a forth column which i'm hoping to adapt your code. which is a the postcode trim (first letters only) which I mentioned on the other thread. I want to copy my excel formula down and then paste it as a value. as your code does above. This would be easier than looking for a vba code.

[TMS]

Please Login or Register  to view this content.

[mtilbury]

Perfect thank you.

I have opted for a different approach. But your code works create. In the end I decided to store the forumla in line one. Then use a vb code to copy that one cell down the whole column. this automatically does the lookup for each row. The second part of the code copies the whole column and paste just as a value. Its super quick an not looking up each time i save.

[TMS]

You're welcome. Thanks for the rep.

Fair enough; glad you have a solution.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.