Loop to find a criteria within range, then output results to individual lines

Good Afternoon,

I have several worksheets within my workbook, each relating to stock, or transactions.

My sheets in question are as follows:

1. Invoice
2. Sales
3. Clients
4. Stock
5. Purchases
   
   
   
   
   

For the purposes of this question, Purchases will not be used.

I am looking to write data from sheets 2, 3 and 4 to sheet 1 (invoice).

I have previously tried using the following code:

Please Login or Register  to view this content.

My issue is that this code isn't working as planned (or at all).

My intention with this code is to search the "Sales" sheet on column B to find a sales invoice number which matches the invoice number inserted on the userform, and then copy across the value from column A (which is a stock number) to a cell on the invoice. This is then to repeat, on a new line for each case of the Sale invoice number, as there is likely to be more than one stock number per sale invoice.

Firstly, can anyone tell me why the code above does not function to do this, and secondly, is there a better or more efficient way to accomplish this?

I have attached the book for those who wish to look.

Hi there,

I think the first problem is in the line:

Please Login or Register  to view this content.

The cells on the Sales worksheet contain Integer or Long values for the SaleID numbers, but the SaleNumber textbox on the CreateInvoice form contains a Text value. As a consequence the If statement always returns a value of False, so the value is never copied to the Invoice worksheet.

To overcome this it is necessary to convert the value contained in the SaleNumber textbox to a Long value before testing it.

I've rewritten the "CommandButton1_Click" in the "CreateInvoice" module as follows, and it seems to do what you want. I've also restricted the number of SaleID cells which are tested - only those cells in the UsedRange of the column are now tested instead of every cell in the entire column.

Please Login or Register  to view this content.

Your project seems an interesting one and I can probably suggest several other changes which would make your code more robust and more easy to maintain. If you're interested, let me know and I'll see what I can do.

In the meantime, I hope the above helps - please let me know how you get on.

Best regards,

Greg M

Thank you so much Greg!

The code you have given me works wonders, and I can now tailor this for other functions that the invoice requires.

The whole book has been written by me, as a complete novice to VBA, and with only intermediate knowledge of excel, and so there are no doubt things that could be improved vastly.

If you're able to provide any suggestions, that would be great. One of my main concerns is that this will soon become very sluggish, since our current stockbook which will be converted to this currently has in excess of 7000 stock entries, each of which would require entries in the other sheet.

Thanks again
Ralph

Hi Ralph,

Many thanks for your feedback.

Your code is impressive if this is your first venture into VBA!

In the attached workbook you'll see that I've completely rewritten the code associated with the "Create Invoice" form with a view to keeping as much separation as possible between the code associated with the form and the code associated with querying and updating the worksheets. This is considered good programming practice - the form is now very loosely coupled to the worksheet and can therefore be reused in other situations with the minimum amount of recoding.

In general, the code associated with updating a worksheet should "know" as little as possible about the controls present on a form, and similarly, code associated with a form should "know" as little as possible about numbers of worksheets, worksheet layout etc. Not surprisingly this makes the code more complex initially, but (and it is a BIG "but") subsequent modifications to worksheet layouts, additional user requirements etc. can be accommodated much more easily if the code is structured correctly to begin with.

Have a look at the code in the attached workbook and see what you think. If there are some points which you don't understand, let me know and I'll see what I can do about explaining them. If you think it would be useful I can try to rewrite the code for the other forms using the principles I outlined above.

I won't be online for a few days, but let me know what you think and I'll reply as soon as I'm back online.

I hope the above and the attached workbook are of some use to you - your comments would be welcome.

Regards,

Greg M

Hi Ralph,

Thanks for your messages regarding the workbook.

I've rewritten the code associated with the "Sale" form. Have a look at the attached workbook and see what you think - I hope I haven't taken too many liberties with your original idea! (By the way, I think we need a better definition of what is a "new" Customer - we can discuss this in another message or in an email if you'd prefer.)

I haven't had a chance to look at your list of "extras" yet, but I'll see what I can do. In the meantime, if you're happy with the new version of the "Sale" form, let me know and I'll try to rewrite the "Purchase" form along similar lines.

I'm in a bit of a rush at the moment so I don't have time to explain what I've done, but if there's anything you're interested in / puzzled by please let me know.

In the meantime, best regards,

Greg M

Hi Ralph,

Latest version attached - one more bug out of the way!

Regards,

Greg

Originally Posted by Greg M

Hi Ralph,

Latest version attached - one more bug out of the way!

Regards,

Greg

Thanks Greg.

Firstly, let me just mention that I officially love you. The changes are much more user friendly, and intuitive.

Will just put a list here of notes / errors in the current document (if any)
Small user issue: when creating a new customer, you must close the form and reopen before being able to select the newly created customer for a transaction.
Other than that, this is all great!

How may I help, with the query regarding a new customer?
Thanks again
Ralph

Hi Ralph,

Thanks for your feedback - I'm glad to see that I'm getting something right!

Thanks also for pointing out the problem regarding closing/reopening the form before a newly-created customer could be used. I think the attached version of the workbook will overcome that problem.

Regarding the condition(s) which determine a whether or not a customer is "new", I presume each customer name is not necessarily unique - i.e. you could have a "Kim Hawkes" in Liverpool and another "Kim Hawkes" in London. In view of this, is it appropriate and/or sufficient to check the combination of Customer Name and Customer PostCode for uniqueness before adding the new customer to the Clients worksheet?

I hope to be able to start working on the Stock Purchase form tomorrow - I've played with it a little bit in the attached workbook, but there's still quite a bit of recoding to do.

In the meantime, any comments are welcome.

Best regards,

Greg

Thank you Greg,

The fix appears to have worked my end!

I'm getting an error upon opening the workbook, although it doesn't appear to be affecting anything.

The error reads:
"Purchase Orders - Auto_Open routine

An error was encountered during execution of this routine".

As I say, this doesn't appear to affect the functionality, but thought I should point it out regardless in case it links to a problem further down the line.

You're correct in that customer names are not unique, and so this was the reason I was looking up clients in the drop-down list with a combination of Postcode and name. I believe that this would be sufficient verification.

All the best
Ralph

Hi again Ralph,

Thanks for pointing out the error in the "Auto_Open" routine - I think the attached version should have cured that problem.

I've rewritten the code for the "Sale" and the "Purchase" forms - please have a look and let me know if they're functioning the way you want them to. If they do what you want, the code can be streamlined a bit further, but I want to be sure they meet your requirements before I proceed.

I've added some data validation criteria to ensure that all required data are entered for a new Client.

Can you please check also that the Purchase Invoice ID is being entered correctly on the "Purchases" worksheet when the quantity of the items being purchased is greater than one?

If you've any comments/suggestions/requirements please let me know either here or in a personal message.

Regards,

Greg


P.S. After many attempts I was unable to upload the file to this thread. I'll try again tomorrow, but if you want to send me your email address in a private message I'll email the workbook to you.

Greg

Hi Ralph,

I finally managed to upload the workbook!

Thanks for your message - if I've problems in the future I'll email the workbook to you at the address you sent me.

In the meantime, please take a look at the attached workbook and let me know what you think.

Regards,

Greg