How to improve speed of VBA code while using Vlookup function

Hi
Once again i am glad to come back into the forum which provides lot of help and knowledge. I am trying to use Vlookup function with in VBA code for obtaining data from sheet "SD Consolidation" into a new sheet PreparationfileSD which has unique list of order information in column "A2:A". However while using following code it is very slow and keeps running for a long time.

Is this formula right? and any possibilities of improving speed with alternative code?? I need to apply Vlookup function because column structure of sheets SD Consolidation and PreparationfileSD are different.

Would be happy to receive feedback and support on this request.

Thanks again,

Narasimharao

I have used 2 named ranges as below
SD consolidated data = OFFSET('SD Consolidation'!$B$3,0,0,COUNTA('SD Consolidation'!$B:$B),7)
SDFinaldata = OFFSET(PreparationfileSD!$B$2,0,0,COUNTA(PreparationfileSD!$B:$B),16)

Please Login or Register  to view this content.

Please try this..

Please Login or Register  to view this content.

If still slow you can use other method..

Originally Posted by Narasimharao Nandula

...I have used 2 named ranges as below
SD consolidated data = OFFSET('SD Consolidation'!$B$3,0,0,COUNTA('SD Consolidation'!$B:$B),7)
SDFinaldata = OFFSET(PreparationfileSD!$B$2,0,0,COUNTA(PreparationfileSD!$B:$B),16)

Please Login or Register  to view this content.

I consider myself a VBA noob, so someone please correct me if I make an errant statement...

Your Table3 (aka: SDFinaldata) is set up for dynamic rows... but it is a static 16 columns wide.

The "For Each cl In Table3...Next cl" loop does a vlookup for each cell (i.e. however many rows it is TIMES 16)... not just one column of data... so if you only want to lookup one column, it is taking 16 times longer than necessary.

Yes!, you can use any col for Criteria and just change the col name.

Hi All,

Thanks for replies. I think my earlier query was not properly enquired / represented. Please find enclosed sample file which i am trying to use. The code suggested by lotuxel worked for the original concept when column A2:A in sheet PreparationfileSD is having numbers in the range of B3:B of SD Consolidation sheet.

But the complication has got little increased because some of the numbers in B3:B are duplicate leading to same value after lookup with VBA. But they belong to different PC code (A3:A)as shown in example file.

Kindly help me on this.

Thanking you in advance,

Narasimharao

Finally, I didn't catch your points.
Would you pls advice the file with result completed...

Hi Lotuxel,

Thanks for your prompt reply again. The original code that you gave me worked. However my data in column A2:A in sheet "PreparationfileSD" has got changed which is not matching to "B3:B of SD Consolidation" sheet. So Vlookup function is not working now as we have to merge Column A & B of "SD Consolidation" sheet to compare with A2:A in sheet "PreparationfileSD".

I have made a different macro to make SD Consolidation sheet. so can't change the format there in that sheet. Kindly suggest any other solution based on enclosed example file.

Best regards,

Narasimharao

Narasimharao,
Are you trying to match column A of sheet PreparationfileSD with column A of sheet SD Consolidation? They are not exact match. Which columns do you want to copy from sheet SD Consolidation in to sheet PreparationfileSD?

So What is your target to present after compare matched.?

Hi AB33,

Nice to get reply from you. Earlier i had got support from you. Yes i am trying to match column A of PreparationfileSD with column A & B of SD Consolidation. In normal vlookup function its easy to write this code, but with VBA i am fumbling to handle the ranges. Kindly suggest.

Thanks again.

best Regards,

Narasimharao

Hi Lotuxel,

My target is finally merge another sheet with PreparationfileSD sheet, (both sheets have same column structure) and transfer the entire merged work sheet into different files. I am already able to perform such function with separate VBA code. Once i get a solution, i would paste total solution that I have worked out. May be that would help others of this forum.

Thanks again and look forward for your suggestions on my latest post.

best regards,

Narasimharao

Narasimharao ,
You have not answered my question

How do you match SG10100 with any SG102313600000948?
I have not looked at the Vlookup, but I suspect you are looking for not exact match.
If it is partial match, which columns do you want to copy in to? And what do you want to do with duplicates?

HI AB33,

I want to perform Vlookup for A&B columns of SD Consolidation together to avoid duplicates. If you carefully observe row 24, 25 & 26 have different codes in column A even though Column B is same. As i mentioned Vlookup for a cell is possible to write. But i want to perform V lookup for a range through VBA.

As i mentioned the code give by Lotuxel was working fine with single column (in this example, column B of both sheets are easily comparable with Vlookup.)

I am sorry if i am confusing you. Hope above is little more clear, i am trying to make vlookup for column A of sheet PreparationfileSD with columns A & B of sheet SD Consolidation.

Look forward for your advise,

best regards,

Narasimharao

Narasimharao ,
Okay!
So, if concatenate column A and B of sheet SD Consolidation, do I get an exact match with column A of PreparationfileSD?
If it is, which column do you want to return? Is it a single column, or multiple columns?

I have found 10 match as per post 14, now the only issue left is which column/s you want to return as you have columns B-H

Jhren,
Your code works, but you may to include iferror if there is no return(No match).

You are not hiding the errors, instead return a nil value as opposed to #N/R

Originally Posted by AB33

You are not hiding the errors, instead return a nil value as opposed to #N/[A]

Which amounts to "hiding the error"...!!!

If the error is expected, it would be a different matter...

If there is no return the code returns error(#N/A), so instead of error, the return would be nil(0). That is exactly what the iferror function does.

So if I always add error handling to display "nil" value when the "regular expression" evaluates to an error, at what point should I automatically assume blank cells indicate an error within my work???

What if my regular expression could also evaluate to "nil" value... how would I know whether there was an error or not???

I am not oblivious to what you are saying, but just because you get an error doesn't mean we should automatically add error handling so the cell shows "nil" value...!!!

Try this line in your code(Replace the line you have with this one) and see what I meant.

Please Login or Register  to view this content.

I know what you mean. I don't have to try it. It will be up to the OP whether he/she wants to add error handling to the formula.

To give you a simple demonstration of my point, enter in any cell: =IFERROR(IF(1>2,"",NA()),"")

Hi All,

The code suggested has worked perfect for fetching values from respective location to desired sheet. However the purpose of my requirement was to speed up VBA code. Presently this code has taken almost 8 minutes to compare around 10,000 rows and fetch correct information to respective columns.

If there is any faster way it would be nice. Otherwise based on your feedback, i will close this request and change to "Solved".

Code which i have used is appended for your review.

Thanks again, With best regards,

Narasimharao

Please Login or Register  to view this content.

Narasimharao,
I am now confused!
Why do you have all lines in your code, while few lines could do the job, unless I am missing?

Jhren's, code looks at column A and returns column G.
I am not surprise the code takes 8 minutes to process as you have lots of select in the above code.

I was referring to Jhren's code

Please Login or Register  to view this content.

AB33,

The code you posted was a recorded macro in Module2 (which I forgot to delete ). I used it to get the R1C1 conversion of the formula I had entered in the GUI. The actual code was in Module1. It appears Narasimharao adapted it to do multiple columns. It might speed thing up to switch screen updating off while his code is running. There's also ways to cut down on the selecting process, but before we get into that, some of his adaptations appear anomolous(?) in that his adaptations will return the same results in two column pairings (two of the array formulas have 5 at the end, and two have 3).

Hi AB33,

Yeah, Jhren's suggestion was with 2 options. One code with name "FnlData1" another with name "Macro1". both have worked for 1 column. But as i mentioned or you could have noticed in my code, i am using 6 columns from sheet SD Consolidation and populating data into 8 columns of sheet PreparationfileSD. As no. of rows are more than 9,500 code has to run through each column one by one to go to next column.

I am little confused on following code when i want to repeat the formula for different columns (H, J, K, L, M, N, O, P) in PreparationfileSD.

Please Login or Register  to view this content.

As i mentioned the solution is working, however it is taking little longer than expected. Will wait for your suggestions.

Best regards,

Narasimharao

Originally Posted by Narasimharao Nandula

Hi AB33,

Yeah, Jhren's suggestion was with 2 options. One code with name "FnlData1" another with name "Macro1". ....

I am little confused on following code when i want to repeat the formula for different columns (H, J, K, L, M, N, O, P) in PreparationfileSD.

Please Login or Register  to view this content.

...

Completely disregard Macro1

For consideration of speeding things up, please elaborate on sheet/column to sheet/column assignments...

Narasimharao,
I have asked not once but three times, but you keep not answering my question. Look at my replies. I have asked you which columns do you want to copy if there is a match? I have find out the answer from Jhren's . The code returns a single column (G). Now, I see you want to return more columns. If there is match, which columns from sheet SD Consolidation do you want to copy in to PreparationfileSD? How hard is to understand this simple question?

Hi Jhren and AB33

Sorry for the confusion that got created. I want to populate data as follows

Column G from sheet SD Consolidation have to go to G column of PreparationfileSD
Column H of from sheet SD Consolidation have to go to H column of PreparationfileSD
Column C of from sheet SD Consolidation have to go to O column of PreparationfileSD
Column C of from sheet SD Consolidation have to go to P column of PreparationfileSD
Column D of from sheet SD Consolidation have to go to N column of PreparationfileSD
Column E of from sheet SD Consolidation have to go to K column of PreparationfileSD
Column E of from sheet SD Consolidation have to go to L column of PreparationfileSD
Column E of from sheet SD Consolidation have to go to J column of PreparationfileSD

At present no. of rows in Column A of both Sheets SD Consolidation and PreparationfileSD are 9474.

Hope above provides more clarity.

Best Regards,

Narasimharao

The attached should do. As you have many columns, I might have mixed columns. Try it and will adjust if needed.

See if this is any faster...

Please Login or Register  to view this content.

Note this adds column R to Prep' sheet.

Originally Posted by Narasimharao Nandula

...
Column G from sheet SD Consolidation have to go to G column of PreparationfileSD
Column H of from sheet SD Consolidation have to go to H column of PreparationfileSD
Column C of from sheet SD Consolidation have to go to O column of PreparationfileSD
Column C of from sheet SD Consolidation have to go to P column of PreparationfileSD
Column D of from sheet SD Consolidation have to go to N column of PreparationfileSD
Column E of from sheet SD Consolidation have to go to K column of PreparationfileSD
Column E of from sheet SD Consolidation have to go to L column of PreparationfileSD
Column E of from sheet SD Consolidation have to go to J column of PreparationfileSD

Sorry, I was typing up my last post when you posted...

If the Code I posted works for you, you'll have to change the Range column letters and index column numbers as follows:

D = INDEX COLUMN 13
G = INDEX COLUMN 6
H = INDEX COLUMN 7
J = INDEX COLUMN 4
K = INDEX COLUMN 4
L = INDEX COLUMN 4
O = INDEX COLUMN 2
P = INDEX COLUMN 2

PS: Also discovered another change...

In the cluster that includes "Range("G2:G" & r).Formula", change each line of the cluster's "r" to "r+1", e.g.:

Please Login or Register  to view this content.

Originally Posted by jhren

Originally Posted by Narasimharao Nandula

...
Column G from sheet SD Consolidation have to go to G column of PreparationfileSD
Column H of from sheet SD Consolidation have to go to H column of PreparationfileSD
Column C of from sheet SD Consolidation have to go to O column of PreparationfileSD
Column C of from sheet SD Consolidation have to go to P column of PreparationfileSD
Column D of from sheet SD Consolidation have to go to N column of PreparationfileSD
Column E of from sheet SD Consolidation have to go to K column of PreparationfileSD
Column E of from sheet SD Consolidation have to go to L column of PreparationfileSD
Column E of from sheet SD Consolidation have to go to J column of PreparationfileSD

D = INDEX COLUMN 13
G = INDEX COLUMN 6
H = INDEX COLUMN 7
J = INDEX COLUMN 4
K = INDEX COLUMN 4
L = INDEX COLUMN 4
O = INDEX COLUMN 2
P = INDEX COLUMN 2

I think I messed that up. Should be...

G ~ 6
H ~ 7
J ~ 4
K ~ 4
L ~ 4
N ~ 3
O ~ 2
P ~ 2

Originally Posted by jhren

I think I messed that up. Should be...

To avoid confusion, the entire revised and updated version of my code...

Please Login or Register  to view this content.

Hi Jhren and AB33,

Excellent, wonderful. Thank you for your kindest support and improving my little brain's knowledge. I am delighted with both solutions which would defenitely help me.

Thanks again

Best Regards,

Narasimharao

You're Welcome...!!!