Hi
Can someone help modify the macro so that in column G instead of the total per driver we have the equation =D26/F26.
I will need this to happen on each drivers total line in column G
Thanks
Please Login or Register to view this content.
Hi
Can someone help modify the macro so that in column G instead of the total per driver we have the equation =D26/F26.
I will need this to happen on each drivers total line in column G
Thanks
Please Login or Register to view this content.
Last edited by TERRI LEE; 09-19-2012 at 05:55 AM.
Hi
I have attached a copy of the new columns I need.
Add in columns H - S as per schedule. These formulas only need to be on the total lines per driver.It must automatically work with its formulas.
In column S - I need a formula for the calculation please.
Then over all I would like the document to have the total lines in Bold Red as I have done here manually. I would like the font to all be sized to "8" as well as the borders done as I have here.
Once all this is done is it possible to copy only the 'totals per driver' rows into a new sheet?
I hope this can be achieved.
Thanks
Terri
Last edited by TERRI LEE; 09-20-2012 at 03:41 AM.
What do you mean by add in columns H - S? The columns already exist. Are they missing from the original data?Add in columns H - S as per schedule. These formulas only need to be on the total lines per driver.It must automatically work with its formulas.
Did you mean Blue and not Red? The file shows blue.Then over all I would like the document to have the total lines in Bold Red as I have done here manually. I would like the font to all be sized to "8" as well as the borders done as I have here.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Yes they are. They are new columns that I added so that you can see what I want.
As long as the colour is different to all the info inbetween.
Last edited by Cutter; 09-19-2012 at 04:40 PM. Reason: Removed whole post quote
What do you mean by add in columns H - S? The columns already exist. Are they missing from the original data?
Yes they are. They are new columns that I added so that you can see what I want.
It will be good if you upload the "before" data o we get a picture of how the data will look in the raw state.
Okay I will in the morning as my computer is at work.
You could possibly pick it up on the previous thread.
Hi
Here is the before data that the macro runs from to generate the new sheet.
Thanks
Terri
Hi
Can someone help modify the macro so that in column G instead of the total per driver we have the equation =D26/F26.
I will need this to happen on each drivers total line in column G
Thanks
Should the subtotals be removed? Which sample file can i use?
Hi
I want subtotals in columns D & F and then the subtotals D divided by F answer in G.
So you want the subtotal part to be changed in the macro in post 1 right?
Yes thats correct
Add this code above the linePlease Login or Register to view this content.
Please Login or Register to view this content.
Yes Thanks it works well.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hi
The thread has not yet been solved as I am waiting to see if any one is able to add to the existing macro to add the extra columns, formulas etc as per post 2
Updated code -
Please Login or Register to view this content.
Hi
It seems to work fine but the last two guys the G column sum is not correct and the formating does not continue to the end. The grand total also has a ref error. I have attached the file that it produces. I then need to add the columns as per post 2.
The #REF error was already present in the data in row 331.
Updated code here -
Please Login or Register to view this content.
Hi
Oops sorry I did see the error and I fixed it. Thanks its working well now. I really appreciate your help. I just need to add the columns as per post 2 now. It will save a lot of time if these are also all automatic.
Thanks Terri
So you want blank columns to be inserted in H-S with the appropriate headers and the calculations in each Total row?
When would the values be entered into columns H -S?
Yes thanks
When do you want the columns to be added? At the beginning of the code?
Hi
I am not sure what you mean here. You wrote the macro for me. It should just be a part of the macro. So when it pulls all this detail it adds the columns and does this work as well. If that is possible
Sorry the macro is in post 23
Last edited by TERRI LEE; 11-14-2012 at 03:03 AM. Reason: Macro already in post 23
What i meant to ask you is - at which stage of the activity should the columns be added? Before the data is consolidated and subtotals are input or after the consolidation but before the subtotals?
After the consolidation but before the sub totals. I will also need sub totals for each section per driver.
You have this condition for column S - If fuel cons is between 2.05 and 2.1 then bonus P1000 and if fuel cons between 2 and 2.05 bonus is P500
Which column should be considered? Diesel Consumption (Averaged) or Diesel Consumption (Calculated)?
I have this code for you which so far does not have the column S populated. Also, the file in post 22 has the calculation D26/F26 for column G, but the file in post 2 has D26/F26 as the calculation for column H and it has the average formula in column G. Which one should i consider? I have left column G as is for now and column H as blank.
Please Login or Register to view this content.
Did you try the code in post 32?
What about this -I have this code for you which so far does not have the column S populated. Also, the file in post 22 has the calculation D26/F26 for column G, but the file in post 2 has D26/F26 as the calculation for column H and it has the average formula in column G. Which one should i consider? I have left column G as is for now and column H as blank.
Hi not yet. I will now.
Hi
I am getting the column headings when I run it but there is no other info.
I ran it on the test file that you uploaded last. Since there was already data, i skipped the consolidation part and ran only the rest and it worked fine. Check the rows containing the subtotals. There should be formulae in there.
There is formula in there but it seems to read from the wrong columns. I think thats the issue. Do you want me to attach the first 10 sheets to run from? I also had changed the original macro to copy a further three columns across do you want me to add that macro?
Sure, that will be good.
Please Login or Register to view this content.
Should the data for these new columns be pulled into the "Driver Analysis" tab while doing the consolidation?
Yes they should
As per the code, the following columns are copied to the destination columns of the Driver tab -
A:B to A:B of Driver
D:H to C:G of Driver
J:K to H:I of Driver
V to J of Driver
What are the conditions for the other columns?
Hi that's correct. I just want the other columns to be added as per post 2. I guess these columns will start from column K onwards.
Sure, i know the names of the destination columns. But where does it start in the sheets 1-31? Is it from column W onwards that need to be copied to column J onwards?
No we are not coping any other columns from sheets 1 - 31. We are just adding the new columns and the formulas into the new sheet once the macro has run.
But you said so in post 39. If not, from where will data come in for the new columns?
So you need formulae put into each row for the new columns and not just in the total rows?
I need formulae put in on each drivers total rows only
Ok, so where will the data come from for the new columns? You will update that later? Just trying to understand the flow here, so asking you these questions.
If you mean for columns A - J the info will be transferred from the necessary columns from sheets 1 - 31 as the macro already does. Then for the new columns that you add as per post 2 which will be in columns K onwards the formulae must calculate these when generating the new sheet. So you will need to add whatever to the existing macro so that it all happens at once.
Sure, the data for A-J comes from the sheets 1-31 which is consolidated. Then the columns K onwards are created with no data. Then the subtotals are inserted for all the columns as per the formulae required. Am i missing anything?
I think thats fine.
The code in post 30 does it all. Which part is not working?
Will try it now and revert.
Hi
I get an error message saying "Microsoft Excel cannot determine which row in your list or selection contains column labels,which are required for this command". If I click continue then it seems to hang and says not responding.
Which line is highlighted?
Hi I can't see a highlighted line as it just says not responding and nothing happens.
In the file that you had given me which had the columns, column M was hidden and there was no header. So when i inserted the columns into your file through the macro, i left column M blank and this is causing the issue. What should be the header name for M? I put in a header as "test" while testing and i didnt get the error. Without the header, i am getting an error.
Hi where did you get the original file from post 2
You had uploaded the document. I used the same one.
Hi
I guess it had to be one from post 2 then, but in this one column M has a heading and it is not hidden so i am not sure now.
Ok just tell me what the header is for column M and whether any formula should go into the total rows for this column. I will get it fixed for you.
I looked back at your post 2 and it was not the spreadsheet that i looked at. But its possible its some other attachment in this long thread. But i did see column M hidden. When the thread gets long, it gets tough to keep track of whats where unless you read the whole thread.
As long as we have all the columns as per post 2 then we will not need column M .
Ok, i found the reason for the confusion.
In the attachment in post 2, column H is blank, so i deleted it. The hidden column was N, which became M after the deletion. I see a formula there -Should this column be retained then?Formula:Please Login or Register to view this content.
Good Morning
The column should be retained and its heading will be "Standard filled *1.05 less filled" .
Could you also please add a two other columns:
- total column that adds up the following columns, "Pula deduction, Std Bonus, Extra Bonus and Discretionary bonuses"
- column that is called "Discretionary Bonus between 2. &2.05" please also insert its formula. So there will be two different discretionary bonuses.
Thanks
Should the 2 new columns be at the end?
Sorry I didn't see your last post. Yes that will be great thanks.
Last edited by TERRI LEE; 11-26-2012 at 04:58 AM.
Can you post here the updated code i gave you? I am not able to find it here in the thread and the code i found was not the complete one.
However i was waiting to see if you could sort it out as it is still not working correctly.Please Login or Register to view this content.
I think it is the same code as in post 38
Thanks Terri
Oh yeah, its the same as post 38. I wanted to wrap it up for you yesterday and looked through the entire thread, but i guess i didnt scroll enough in the code boxes so didnt find the right code.
If you run this a pop up message saying the following comes up: Microsoft excel cannot determine which row in your list or selection contains column labels, which are required for this command.
i also get an error on the following line saying division by zero error 11
Please Login or Register to view this content.
This error comes up when F has a 0 value. Once your data is populated if column F wont have any 0 values, then its fine. Otherwise, we can put a check - like check if F is blank, if it is, put in 0, otherwise put in the calculation result.
Hi
You are right but the formula does not look as if they are correct.There is no formula in column H. Column I reads from J3 which needs a figure permanently entered.Etc.
Hi here is the final code that all seems to be correct now. Please just help with some final tiding up. I wanted the grand totals ( very last line to be in bold red) The grand totals in all the columns are also not correct. please could you correct this. I will attach a copy of the file that is generated. I also wanted range K3:N3 to be in calibri, bold,8 font and outlined.Please Login or Register to view this content.
Thanks
Terri
Last edited by TERRI LEE; 12-14-2012 at 12:33 PM.
Updated code -
The subtotals are automatically inserted through the subtotal feature in excel. Which columns are wrong and i can check them?Please Login or Register to view this content.
The grand totals are wrong from column L to column V
The grand totals are doing the same calculations as the subtotals. Do you want a separate calculation for the grand totals?
Yes I wanted it to sum all the sub totals
But wouldnt the sum of all the subtotals give the wrong figures for columns where we have not used the sum function and used average or some other function?
I guess you are right. I am trying to get a total of what I need to pay out. So I need sum formulas on all columns except where I have calculated an average in sub columns. I hope this makes sense.
So do you want me to clear columns M to P in the grand total row?
Hi
I want Sum totals on columns D,F,J,K,M,N,O,P,Q,R,S,T,U and V
And average total on column G
Is it also possible to show only two decimal places through out the worksheet?
Thanks
Terri
Updated code -
Please Login or Register to view this content.
Last edited by arlu1201; 12-17-2012 at 02:14 PM.
Thanks
It just the totals thats left to be sorted now!!!!! Yea almost done
I have made a small change the code i just posted. Please try and let me know.
It gives me a compile error syntax error in the following line
i).value not like "grand Total" then
Sorry a typo at my end. Try the code again please.
Cool works now , but i need the average for column G
Edited the code again. Please try it and let me know.
Yip works great, thanks for all your help. I really appreciate it. You can rest in peace now I wont be bugging you to soon. I have managed to excel advanced training so know i want to take time out next year to do Vba training. Thanks again!!!!!
Oops just noticed my dates have changed to numbers??????
Last edited by TERRI LEE; 12-17-2012 at 11:24 AM.
No problem at all. Am glad it worked.
Sorry just noticed my dates have changed to numbers????
Ok, we will need to separately format the columns with numbers and the columns with dates.
Only column A has dates
I have updated the code. You can try now.
Works great thanks so much for your help!!!!
You are welcome. Thanks for the rep.
Hi
I just discovered that from column J the rand total does not include the very last sub total in its calculation. Could you help fix this please?
Try this code and let me know -
Please Login or Register to view this content.
Hi
Sorry I have been on leave. The new macro still has the same issue - no idea why But I need to fix it!!!!
Hi
This is the outcome of running the macro.
This is the code I am using as we made a few column changes.
Please Login or Register to view this content.
The reason for the discrepancy is - the subtotals in columns L, M, N, etc are actually calculations and not subtotals. While the grand total is a grand total. If you check the calculations in the columns, you will understand. Should i change the formulae in the grand totals line to be the same as the calculation in the subtotal lines?
Hi
I need the grand total row to add all the subtotals in columns D,F and J.
In column K,L,N,O,P,Q,R,S,T,U,V and W, I need it to give me a total
In column G I need an average of the subtotals.
Thanks
Terri
Try this updated code -
Please Login or Register to view this content.
Hi
I looked at the above macro but it is now giving me the calculations in columns L,N,O,P,Q,R,S,T,U,V and W, instead of the totals
It is also giving me a calculation in columns G and K instead of the average of all the subtotals
If you put in the subtotals into columns L, N, O, etc, you will get a mismatch in the calculations. But if you put the same calculation that you put in the cells in that column, then it will give you the proper figure.
Columns G & K should give you the averages.
I manually added the columns and the answers are incorrect so something is not right.
Did you manage to look at this one. Should I maybe start a new post with this query?
Sorry. Let me look into this again. There is something not right with the calculations.
Have you added any new columns? If your work involves adding in new columns frequently, we may have to change the code to do the calculations by the column header and not the column address.
This may take slightly longer time to execute but it can be done.
Hi Arlu
I managed to do the adjustments i needed so not to worry. If you can just try sorting out the total columns for me I would appreciate it.
Thanks
Terri
Ok, should i use the same file that you had uploaded earlier? Are there any changes to the columns that need the calculations?
Hi
The columns for the calculations have stayed the same so you can use the previously uploaded file.
Thanks
Try this updated code - Had to make some adjustments for it to work right -
Please Login or Register to view this content.
Hi
I have attached the sheet that was run. You can see from columns L onwards it does not calculate correctly as it is not adding the last number on row 850. The only totals that are correct from column L onwards are the ones where row 850 was a zero. That means if there was an actual number here we would get the incorrect total.
Sorry
Terri
I ran the revised macro in post 117 and it shows me the correct figures for column L. Did you try the same code? Can you please try again and let me know?
Hi
I ran it and it definetly is not calculating the very last figure in. In the very last column I have done a manual calculation and you will see the difference is 500.00 which is the last figure before the grand total
See the attached - i ran the macro and there is no difference. My macro run is tab 1, yours is tab 2.
Hi
How did you manage to run it without having all the individual sheets?
If you look at you schedule in cell W851 your formulae is =SUBTOTAL(9,W5:W850) but in mine it is =SUBTOTAL(9,W5:W849)
I have no idea why but that is why I am not getting the correct figure.
When you run through the macro in the F8 mode, you can skip some rows, thats how i have been running it all this while for you.
I ran the same macro that i gave you in post 117. Try running it once more and you will see that the issue vanishes.
By the way, i have noticed lately that you attach .xlsm files to your post but there is no macro in there. Arent you running the macro from the same file?
Hi
What do you mean the F8 mode? I have no idea what you are talking about. All my macro are sitting in a personal folder and I run them from their.
Tried running it again and it still does the same . I have no idea what I'm doing wrong
When i am testing the macro, i step through each line of the code which is known as the F8 mode or step through mode. At that time, if you have experience in using VBA, you can exclude some code lines and move forward. But you need to know where to skip.
Can you attach a small sample of file which contains the other sheets as well? Maybe i can try running it from end to end.
Also, try renaming your macro to something else so that you know you are picking the right macro from your personal folder.
Hi
I tried renaming the new macro in my schedule and then I let it run and now it seems to work fine. I will be using it thru out the day so I will give you feed back later.
Perhaps I was running the old macro and that was the issue.
Thanks so much for your help and your patience. It has really help me save alot of time.
Terri
Hi
Sorry it is still doing the same thing. i will attach a full shedule for you to see. No idea what I am doing wrong. The first few columns add correctly and then it goes all wrong.
Just a pointer, it will be good if you delete the older macros (the earlier versions which i gave you) so that you know you are running the right macro each time. Let me check your file now.
The code in your file was not the one i provided you last. There was one small change missing which i input now. Use this updated code -
Please Login or Register to view this content.
Hi
I tried again with your new macro and I am still having the same issue. WHY I have no idea. You will see from column L it goes wrong.
Terri
I was doing some testing. The reason why sometimes it works and sometimes it doesnt, is because you do not have values in the rows between each subtotal. You have values in the subtotal rows. As per the subtotal function, the grand total does not consider the last total. It only includes the last row above the subtotal row (for the last subtotal only).
Hence, we will need to fix this by having the code add up each value. I will work it up and post it here shortly.
I was thinking of putting in a calculated subtotal (calculated by macro) for all the fields from column L onwards (whichever need the calculations). Should i do it from column D onwards instead?
Hi
Columns D, F, G and J formula works correctly. The issue is from column L onwards
Try this updated code -
Please Login or Register to view this content.
Hi
Thanks it seems to be working fine now. Thanks for all your help. I have learnt alot working with you.
Terri
Am glad its solved now. I learnt a lot too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks