Won't complete last Loop

**GrayWolf** · 04-29-2022, 12:47 PM

Hello Everyone,

I have this application working 99%. I'm very new at VBA still so I may not have efficient coding, but it is almost working the way I want.
I am setting this up for someone else to use so that is why it is heavily commented.

*NOTE* the "Excel" sheet data is cleaned and sorted by InvoiceNumber and DepartmentCode MANUALLY prior to running the code. "I'm not that good yet" LOL

What the code does:

In nutshell:
The VBA code copies all the records from A to H, with the same invoice number, on the "Excel" sheet into a copy of the "Master" Sheet in A to H.
Then renames that sheet and saves it.

In Detail:
- The code takes the "Master" sheet and makes a copy
- Then loops through each record in the "Excel" sheet
- looks at invoice numbers and loops to copy all records, from A to H, with the same invoice number from "Excel" over to the newly A to H on the newly created copy of "Master" sheet
- then renames the newly created sheet the invoice number (which in hindsight is probably not necessary now that I think of it)
- once done it adds the excel "Subtotal Feature" to the sheet, saves the sheet as a new workbook then deletes the sheet.

In the end there should be 11 workbooks created and renamed:
CC PTS Ontario Fuel Escalation Charges Invoice 102001-Fuel.xlsx ( 1 record)
Cash Broker PTS Ontario Fuel Escalation Charges Invoice 102002-Fuel.xlsx ( 1 record)
Cash Broker PTS Ontario Fuel Escalation Charges Invoice 102003-Fuel.xlsx ( 1 record)
Cash Broker PTS Ontario Fuel Escalation Charges Invoice 102004-Fuel.xlsx ( 1 record)
Cash Broker PTS Ontario Fuel Escalation Charges Invoice 102005-Fuel.xlsx ( 1 record)
Cash Broker PTS Ontario Fuel Escalation Charges Invoice 102006-Fuel.xlsx ( 1 record)
Ornge Fuel Escalation Charges Invoice 102007-Fuel.xlsx ( 1 record)
UHN Dedicated 440002090 Fuel Escalation Charges Invoice 102008-Fuel.xlsx ( 3 records)
LHC 607012201020 Fuel Escalation Charges Invoice 102009-Fuel.xlsx ( 6 records)
SJHH Hamilton - Charlton - Continuing Care Fuel Escalation Charges Invoice 102010-Fuel.xlsx ( 1 record)
Cash PTS Ontario Fuel Escalation Charges Invoice 102011-Fuel.xlsx ( 1 record)

So works as I expected after about a dozen tests.
The Problem is when it gets to the last invoice number (102011) it doesn't subtotal it and save it.
It creates the sheet for the final Invoice then quits the sub.

I tried adding changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

, which worked, but then errored out because there were no more rows with invoice numbers.

That is my problem. I just need this loop to finish out the last invoice record.

Let me know if you need more clarification. Again, sorry for all the comments too.

Thank you,
Gray

**jolivanes** · 04-30-2022, 12:23 AM

Out of the 11 fields to be filled in on the Master sheet, only two (Invoice Number and Number of Trips) have the same header in Excel sheet.
A couple can be guessed maybe but you better explain in detail but in a concise manner what needs to be done.
The Master sheet should have an example of one of the Invoice Number rows.
You might have noticed that there were close to 60 visitors on your thread but no suggestions which usually means a lack of understanding on what needs to be done because of poor explanation.

**GrayWolf** · 05-02-2022, 06:59 AM

Thank you @Jolivanes.
I have added more detail to my original post in the hopes of getting some results.

**jolivanes** · 05-02-2022, 11:17 AM

Re: CC PTS Ontario Fuel Escalation Charges Invoice 102087-Fuel.xlsx ( 1 record) and the others in Post #1
Where do you find 102087 in your attachment?

**GrayWolf** · 05-04-2022, 08:49 AM

Hello @jolivanes.

My Apologies.
The examples I placed in the text above did not match the sample data in the file I can understand why it is confusing now. LOL.
I have edited the text above with the proper relevance.
The 102087 should have been 102001 from the "InvoiceNumber" column A in the sheet named "Excel"

**jolivanes** · 05-04-2022, 11:27 AM

You have the following Headers in "Master"
Invoice Number. I assume from "Excel" Column A.
Account Number. From where in "Excel"?
Account Name. From where in "Excel"?
Department. From where in "Excel"?
Trip Date. From where in "Excel"?
Reservation Number.
Number of Trips.
Billable KM
Fuel Escalation (Base Chg). Calculated
Fuel Escalation (Billable KM Chg). From where in "Excel"?
SubTotal Fuel Escalation Chg. Calculated

The following ranges need values also.
Posted Disel (Diesel?) Rate at K10. From where in "Excel"?
Fuel Escalation Per KM Rate at K11. From where in "Excel"?
Included KM in Base in K12. Same amount all the time or from where in "Excel"?

Change the "Merge & Center" in G3:K3 to Center Across Selection.
The use of "Merge & Center" is an accident waitng to happen.

And anything else you can clarify.

**jolivanes** · 05-05-2022, 04:35 PM

Still waiting on a response.
Change the attached as required.
See attached

**GrayWolf** · 05-11-2022, 08:38 AM

Hello,
Sorry for the delay. I've been very busy. I am very appreciative of your follow-up.
Here are my Responses to the questions you put above:

I am so sorry - For the record I did not make this invoicing set process and I have little to no say in how the "Master" Invoice looks. I would have kept the headers as similar as possible.
I am just trying to automate it.

Invoice Number. From "Excel" Column A.
Account Number. From "Excel" Column B. (another data massaging error on my part also this cell was truncating the leading 0's)
Account Name. From "Excel" Column C.
Department. From "Excel" Column D.
Trip Date. From "Excel" Column E.
Reservation Number From "Excel" Column F.
Number of Trips From "Excel" Column G.
Billable KM From "Excel" Column H.
Fuel Escalation (Base Chg). Calculated
Fuel Escalation (Billable KM Chg). Calculated from K11 (Fuel Escalation Per KM Rate)
SubTotal Fuel Escalation Chg. Calculated

The following ranges need values also.
Posted Disel (Diesel?) Rate at K10. Comes from an Input Box - this rate changes weekly
Fuel Escalation Per KM Rate at K11. Comes from an Input Box - this rate changes weekly
Included KM in Base in K12. Pretty much same all the time. It may change year to year so i just manually update it.

Change the "Merge & Center" in G3:K3 to Center Across Selection. (owners want this left as is)
The use of "Merge & Center" is an accident waitng to happen. ( I am aware and advised them i first opened the document)

Hope that clarifies.
Gray

**GrayWolf** · 05-11-2022, 09:03 AM

Originally Posted by jolivanes

Still waiting on a response.
Change the attached as required.
See attached

Hi Again,

Looking at this and it does do what I need it to, however when i step through it, especially the array parts I am not able to follow what is going on. I have never worked with arrays before and only been writing VBA for a couple months.
If I need to maintain this code or upgrade it with other requests I'll be lost. Are you able to add any comments. I tend to comment the heck out of my code so i don't forget what i was doing.

TY again,
Gray

**jolivanes** · 05-11-2022, 12:52 PM

See attached

**GrayWolf** · 05-12-2022, 07:37 AM

Thank you very much for all your help jolivanes. I really appreciate your time and effort.

Gray

**jolivanes** · 05-12-2022, 12:35 PM

Your explanation in Post #8.
I thought that the items in the columns might be in different order but it looks like they are all the same from the 1st to the 8th.
By accident, it all worked as required I think.
Good Luck.

Won't complete last Loop

LinkBack

Thread Tools

Rate This Thread

Display

Won't complete last Loop

Re: Won't complete last Loop

Re: Won't complete last Loop

Re: Won't complete last Loop

Re: Won't complete last Loop

Re: Won't complete last Loop

Re: Won't complete last Loop

Re: Won't complete last Loop

Re: Won't complete last Loop

Re: Won't complete last Loop

Re: Won't complete last Loop

Re: Won't complete last Loop

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Step within Macro is taking forever to complete and locks up excel until complete

[SOLVED] VBA Solver Loop while waiting to complete

[SOLVED] For...Next does not complete the loop

Trying to complete a loop within a loop

VBA - Copy and Rename New Sheets from List - Loop until complete

Excel VBA Fails to Complete Subroutine After Finishing For-Next Loop

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions