vlookup and looping vba

[davidktilley]

Hey everyone,

Still trying to get the hang of VBA. I have a set of customer accounts that I need to pull the state in using vlookup. The thing is the customer accounts list is dynamic and changes every week (nfile - 20,000+ lines) and the master file I pull the states from is dynamic and changes every week ( ZCIS.xlsx - 1,000,000+ lines). I am trying to figure out how to speed up my vlookup using a next i or loop construct. Dim nfile allows me to use input date to enter in the date of the file each week since the customer accounts format never changes, simply the date we are looking at the open customer accounts. ZCIS is the customer master file that contains the state I am trying to pull-in. My previous code used a simple .formula and LastRow.filldown.

Please Login or Register  to view this content.

This is my current failed attempt at the do loop construct

Please Login or Register  to view this content.

Any help is greatly appreciated, also let me know if I am approaching the speed issue from the wrong angle. Thanks!

Kyle

[pjwhitfield]

Im probably being dim but you set the last row in variable LastRow2 but then use LastRow in your For loop, shouldnt that be

Please Login or Register  to view this content.

or are you setting LastRow elsewhere in the code?

[davidktilley]

To recap, I have a set of customer accounts that I need to pull the state in using vlookup. One file (nfile) is my customer numbers and their accounts receivable information (typically 20,000+ lines) and the file that has the state(s) listed is the customer master (ZCIS.xlsx - currently at 1,023,128 lines). I need figure out how to speed up my vlookup using a next i or loop construct.

Please Login or Register  to view this content.

Unfortunately I keep getting method 'range' of object'_global failed. Any tips what I might be doing wrong? Thanks!

Kyle