Same vba routine produces different results

Hi Folks,

Can anyone explain this please?

I’ve been using a set of workbooks to control client details and invoicing which I decided to automate.

Opening the “ClientHandler” workbook will create a new folder for the month (if one doesn’t exist) which contains a workbook (“Invoices”) with a work sheet for each regular client. A Userform then displays various options including “New Invoice” which when clicked opens a second form. As the second form opens it populates a combo box from a workbook named “Clients”. When the user selects a client one of two things happen. If no invoice for that client exists a new sheet will be created and named for the client or, if an invoice already exists the user is given the choice of updating that invoice or creating an additional one. When a new invoice is created various values are copied from the “Clients” workbook (address etc).

To populate the combo box when the “New Invoice” form opens I use the code snippet “lastRow = .Range("A65536").End(xlUp).Row” and then loop through all the entries in the “Clients” workbook. All fine and dandy, all the clients appear in the list. The selected entry becomes the string “myClient”.

Because the code for the form is taken from other projects when a new invoice (worksheet) was created the same snippet was used again to find the last row in the “Clients” workbook then loop through to match myClient with the matching value in column A. But it didn’t work.

I found that the variable lastRow in the second instance was returned as one less than the selected entry on the combo box so no extra details were copied from the “Clients” workbook. Bizarrely, If then selected new invoice again and took the option to create a new invoice for the same client all the required data was copied because lastRow was now returned as the combo box plus 1...

I’ve solved the problem by making the variable lastRow public and only performing the code snippet once as the form opens but I have no idea why performing the same routine twice should cause differing values to be returned.

John

Sorry about that.

Anything not declared within the sub-routines is a Public variable.

Please Login or Register  to view this content.

At this point I’ve just realised that having loaded the Public myArray with all the details of the Clients workbook I don’t need to use that workbook again. Dummy! Anyway back to my original question.

By the time we enter this sub-routine checks have been made for existing invoices.

Please Login or Register  to view this content.

When the workbook “Clients” closes it checks for new entries and unless it finds any closes without saving so from this form it closes without saving.

I realise my code is inelegant but it works apart from differing values of lastRow. Actually the answer to my question isn’t important except that it bugs me.