Vlookup data and compare variances

[leanne2011]

Hi

I have begun the macro coding for a stock report. The macro report has on sheet 3 the minimum stock numbers for each item which has a 5 digit stock code which I was hoping to vlookup in a weekly report to give the variances - which will in turn give an indication of which items need restocked/replenished etc and displayed on sheet 2.

Here is my code so far - can someone please please help me with how to use the v lookup on a loop ? or is there an easier way.

Please Login or Register  to view this content.

The export file will be a weekly report that will be used to compare against the permanent file held on sheet 3 in "PAULS STOCK WORKSHEET.xlsm" I only want those stock items which are negative reported in sheet 2

PAULS STOCK WORKSHEET.xlsmXL_Export.xlsb

Many thanks in advance

[leanne2011]

Please can someone help?

I need to the lines of stock data numbers appear from the export report appear in sheet 3 of the stock report(being what the stock should be at a minimum) - if it does I need the variance i.e. current stock less minimum stock numbers and reported in red if negative.

eg Pauls Stock report details 1 safety helmet but weekly report states 26 so need to see -25

Thanks a lot for any help or direction

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[leanne2011]

This is perfect - thank you v much for responding. How could I add in a column for current stock (from the stock report) and the minimum stock required (in the pauls stock report sheet 3) ? The variance generated would then be the difference between these two as you have correctly calculated.

[JOHN H. DAVIS]

You're welcome. Glad to help out. Delete this line just before your End Sub - Columns("F:G").delete. I didn't know you wanted to keep them.

[leanne2011]

PAULS STOCK WORKSHEETorig.xlsmJohn - thanks again for your time. I modified it slightly but when I delete out the columns("F:G").delete it doesn't populate it and its gotta be what I have done. I attach the code so u can see ? and how I need the spreadsheet to be ?



Also the sheets have been names XL_Export.xlsb is this going to matter if they are named differently each week - this is the only report name that would change regularly.


John - sincere thanks for your help

Please Login or Register  to view this content.

[JOHN H. DAVIS]

I didn't try to re-incorporate it into your code. But see if this helps?

Please Login or Register  to view this content.

Also, the Export file how is it renamed every week? Do you use a standard naming convention for this?

[leanne2011]

Hi John

Absolutely spot on - not too bothered about the file name I will get around it. One thing that seem to be an issue - it only reports the data in sheet 2 for 14 rows of data but there is loads more. Would I need to sort the data on both worksheets - could it be the vlookup has stopped working because the data is not in order ?

thanks
Leanne

[JOHN H. DAVIS]

That shouldn't be an issue, but try this instead.

Please Login or Register  to view this content.

[leanne2011]

Hello - I changed the code to the above but no difference - still only 14 rows of data ? any ideas

[JOHN H. DAVIS]

Can you attach a sample, where this is happening?

[leanne2011]

hi sure - here is the output and the code is within the macro tabPAULS STOCK WORKSHEETorig.xlsm

[JOHN H. DAVIS]

hi sure - here is the output and the code is within the macro tabAttachment 304208

It looks like it should be 14? Or do you want it the other way around? If you autofilter on Column G custom < 1. You can see which items should be carried over too Sheet2.

[leanne2011]

Ah thats why - I dont need less than one. finally then (i promise and so sorry to keep on) if I take out the bit <1 how then do I take care of the bits where there are no values and zero data - it doesnt produce a variance

Thanks John

[JOHN H. DAVIS]

I think we have a communication problem. Please explain what items you want to copy over to Sheet2 and how we will determine that.

[leanne2011]

John - it all works fine and my mistake. This is all solved - many thanks.

Kind regards
Leanne

[JOHN H. DAVIS]

You're welcome Leanne. Glad to hear it works for you, and thanks for the feedback.