Need help with VBA Vlookup...keep getting 1004 runtime or code skips

**John2810** · 03-05-2010, 05:11 AM

Hi everyone.

I am working on a spreadsheet (I call it Margin Monitoring, or MM) which will automatically look through two different workbooks (Billing and Pricing) for changes.

The information from Billing workbook is the latest information, that needs to be updated into the Pricing workbook.

In summary, MM will ask for a Billing file to open, from which it will copy everything and paste it in a new worksheet in MM. It will then cycle through a user-defined folder of Pricing workbooks to update.

I am using VBA Vlookup to look for the changes, and I am using concatenating to do this. For both Billing and Pricing workbooks, I will concatenate the 3 columns needed to lookup the correct value.

Funny thing is, it seems to be working fine at first, but now it is totally broken, stuck at Vlookup and giving me that 1004 runtime error or just skipping all the reminding code altogether. I then opened up another module within the worksheet with just the Vlookup code along with the necessary variable declarations...and it works fine. The EXACT same code fails in the main module however...

I have read up on 1004 and set in On error handlers to process #N/A responses. But I'm still stuck....and pulling my hair for this!

Please advice. stated below is the section of the code I am having trouble with, along with all the variable declarations.

I have also attached the files needed.

-------------------------------------------------

Please Login or Register  to view this content.

**teylyn** · 03-05-2010, 06:01 AM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

**Andy Pope** · 03-05-2010, 06:03 AM

The only error generated for me was in the RemoveEmptyRows routine.
Caused by none of the cells being blank.

Please Login or Register  to view this content.

I normally create my own function when using the worksheet functions that return possible error codes. Then within my function I can handle those return errors with error trapping.