How to Exit Do Loop and go to other worksheets ?

Hello,

I'm writing a macro to post a monthly income journal. The main macro uses vlookup to search for an account number that corresponds to a code, and then inputs the debit or credit based on the day of the month. I need the macro to look through at least 6 worksheets, but I'm having trouble getting the macro to loop through more than the first worksheet ("HOTEL") after it finds a blank cell. I'm using a "Do Until" loop and trying to "Exit Do" and have the macro go on to the next worksheet ("WKST"), then the next worksheet, etc., but I can't make it happen. When it finds a blank cell and exits the loop, it moves down to the DEFINELOCATION macro, but I don't want that yet if it hasn't found the correct account number on the first worksheet.

Thank you for any ideas on this! Here's the code I have:

Please Login or Register  to view this content.

HaHoBe,

I've added your code to the FINDACCT macro, and I've tried to adapt it to what I'm trying to do, but have failed.

I would like the code to look at the 6 worksheets ("HOTEL", "WKST"..."F&B") and find the matching account number from the DAILY worksheet. If the first account number isn't found, I want it to look at all the others. After each account number is found, the other sub macros DEFINELOCATION, PASTEVALUE OR ADDVALUE should post the corresponding debit or (credit). This worked for me on the "HOTEL" worksheet, but I couldn't get it to look through the other 5 worksheets. Perhaps these other macros are now not needed based on your code?

Any other information you could provide would be very helpful to me. Thank you.

Hello HaHoBe,

The macro is supposed to copy each debit and (credit) from the DAILY tab into one of the the 7 sheets you've listed in your array. The DAILY tab is new data that is imported each day from a .txt file, so each day there are new debits and (credits). If it's the 1st day of the month, it should copy all the debits and (credits) into the 1st row of the correct sheet. If it's the 2nd day, copy into the 2nd row, etc. The CHARGE CODE tab is a table of all the existing codes and Acct numbers.

For example, on 8/1/14 the first amount on the DAILY tab is an ($89.15) credit under code 14. This amount should be copied as ($89.15) to the HOTEL tab, cell M13, because the Acct number in column C of the CHARGE CODES tab matches cell M13 on the HOTEL tab. The sixth amount on the DAILY tab is a ($1,176.31) credit under code 113. This amount should be copied as ($1,176.31) to the WKST tab, cell F13. Code 903 on the DAILY tab is a $1,021.53 debit that should be copied as $1,021.53 to the ADJT tab, cell M13. Etc., Etc.

Each debit and (credit) has a unique Acct number that is matched to the same unique Acct number in a column on one of the 7 sheets.

I can try to clarify further if you need it. Thank you again for trying to solve this for me!

Regards,
Jason

Hello HaHoBe,

Wow, except for one 'run-time error: type mismatch', you got it! This is simply fabulous!

The debits and credits were reversed as you suspected, but I just switched the "+" and "-" and this was fixed. To answer your question, each code will only appear on one sheet.

It does post all the debits and credits, but afterwards it gets stuck with the 'run-time error' type mismatch' here:

Sh.Cells(lngRowNr, var).Value = Sh.Cells(lngRowNr, var).Value + wsDaily.Range("D" & lngCtr).Value

Not sure what needs to be fixed, though, since all the amounts post correctly on the correct sheet.

I do have one request. Is there a way to have the debit and credit amounts in columns D & E on the DAILY tab to not turn to zero, and instead leave the amounts there?

Thank you,
Jason

Hi HaHoBe,

Yes, the credits and debits have been transferred and the error shows up within the same run of the macro.

Thanks,
Jason