VBA code to add data in the list that meets certain criteria

Hi Guys

I have a Access table that stores BatchNo,Scandate,NewBatchNo . As I can't attach Access database here so I have exported data from Access to Excel in Sheet2 to give you the idea how the data is stored in Access table.

Now I have to write code in Excel/VBA that will check the last NewBatchNo in the table . In the attached workbook its 194389. Now vba code should check the NewBatchNo which are blank before 194389 . Now in the records where NewBatchNo is blank , it should add the corresponding BatchNo,ScanDate in the listbox1 in userform1.

e.g Sheet3 in the attached workbook stores Bathcno and Scandate of those records where NewBatchNo is null and before the Last used NewBatchNo which is 194389 in our example.

ANy help would be much appreciated.

Thanks

Can anyone please help me in this? I want to check the last used NewBatchNo in the table and then before that NewBatchNo, I have to check the NewBatchNo blank. To all the records where NewBatchNo is blank , add corresponding BatchNo and ScanDate in the listbox using VBA.

Any help would be much appreciated.
Thanks

Run this code on opening you userform (and Listbox), by placing it in the userform module

Please Login or Register  to view this content.

Regards Mick

Thanks Mick. It worked fine. How can I display the column heading BatchNo,ScanDate in the listbox?? Many Thanks

Try this:-
NB:- The headers are added in the code, they are not true headers as you can select them, but they will perhaps do !!!
To add actual Headers you need to set the data as as "RowSource" or "Listfillrange".

Please Login or Register  to view this content.