+ Reply to Thread
Results 1 to 137 of 137

Adding columns to a macro

  1. #1
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Adding columns to a macro

    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.
    Attached Files Attached Files
    Last edited by TERRI LEE; 09-19-2012 at 05:55 AM.

  2. #2
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Adding columns to macro

    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
    Attached Files Attached Files
    Last edited by TERRI LEE; 09-20-2012 at 03:41 AM.

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.
    What do you mean by add in columns H - S? The columns already exist. Are they missing from the original data?

    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.
    Did you mean Blue and not Red? The file shows blue.
    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]

  4. #4
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    What do you mean by add in columns H - S? The columns already exist. Are they missing from the original data?

  6. #6
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Yes they are. They are new columns that I added so that you can see what I want.

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  8. #8
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Okay I will in the morning as my computer is at work.

    You could possibly pick it up on the previous thread.

  9. #9
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi

    Here is the before data that the macro runs from to generate the new sheet.

    Thanks
    Terri
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Should the subtotals be removed? Which sample file can i use?

  12. #12
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi

    I want subtotals in columns D & F and then the subtotals D divided by F answer in G.
    Attached Files Attached Files

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    So you want the subtotal part to be changed in the macro in post 1 right?

  14. #14
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Yes thats correct

  15. #15
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Add this code above the line
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Yes Thanks it works well.

  17. #17
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  18. #18
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  19. #19
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Updated code -
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.
    Attached Files Attached Files

  21. #21
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    The #REF error was already present in the data in row 331.

    Updated code here -
    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  23. #23
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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?

  24. #24
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Yes thanks

  25. #25
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    When do you want the columns to be added? At the beginning of the code?

  26. #26
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  27. #27
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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?

  28. #28
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    After the consolidation but before the sub totals. I will also need sub totals for each section per driver.

  29. #29
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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)?

  30. #30
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  31. #31
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Quote Originally Posted by arlu1201 View Post
    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)?
    Column "G" should be considered.

  32. #32
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  33. #33
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi not yet. I will now.

  34. #34
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi

    I am getting the column headings when I run it but there is no other info.

  35. #35
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  36. #36
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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?

  37. #37
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Sure, that will be good.

  38. #38
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Please Login or Register  to view this content.
    Attached Files Attached Files

  39. #39
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Should the data for these new columns be pulled into the "Driver Analysis" tab while doing the consolidation?

  40. #40
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Yes they should

  41. #41
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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?

  42. #42
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  43. #43
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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?

  44. #44
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  45. #45
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    But you said so in post 39. If not, from where will data come in for the new columns?

  46. #46
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Quote Originally Posted by arlu1201 View Post
    But you said so in post 39. If not, from where will data come in for the new columns?
    The new columns you are adding need formulas put into them that calculates the results.columns A -J info comes from sheets 1 - 31. I hope this clarifies it, sorry I caused confusion.

  47. #47
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    So you need formulae put into each row for the new columns and not just in the total rows?

  48. #48
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    I need formulae put in on each drivers total rows only

  49. #49
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  50. #50
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  51. #51
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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?

  52. #52
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    I think thats fine.

  53. #53
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    The code in post 30 does it all. Which part is not working?

  54. #54
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Will try it now and revert.

  55. #55
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  56. #56
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Which line is highlighted?

  57. #57
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi I can't see a highlighted line as it just says not responding and nothing happens.

  58. #58
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  59. #59
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi where did you get the original file from post 2

  60. #60
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    You had uploaded the document. I used the same one.

  61. #61
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  62. #62
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  63. #63
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  64. #64
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    As long as we have all the columns as per post 2 then we will not need column M .

  65. #65
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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 -
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Should this column be retained then?

  66. #66
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  67. #67
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Should the 2 new columns be at the end?

  68. #68
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  69. #69
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  70. #70
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Please Login or Register  to view this content.
    However i was waiting to see if you could sort it out as it is still not working correctly.

    I think it is the same code as in post 38

    Thanks Terri

  71. #71
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  72. #72
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  73. #73
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    i also get an error on the following line saying division by zero error 11

    Please Login or Register  to view this content.

  74. #74
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  75. #75
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  76. #76
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Please Login or Register  to view this content.
    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.

    Thanks
    Terri
    Attached Files Attached Files
    Last edited by TERRI LEE; 12-14-2012 at 12:33 PM.

  77. #77
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Updated code -
    Please Login or Register  to view this content.
    The subtotals are automatically inserted through the subtotal feature in excel. Which columns are wrong and i can check them?

  78. #78
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    The grand totals are wrong from column L to column V

  79. #79
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    The grand totals are doing the same calculations as the subtotals. Do you want a separate calculation for the grand totals?

  80. #80
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Yes I wanted it to sum all the sub totals

  81. #81
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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?

  82. #82
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  83. #83
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    So do you want me to clear columns M to P in the grand total row?

  84. #84
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  85. #85
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Updated code -
    Please Login or Register  to view this content.
    Last edited by arlu1201; 12-17-2012 at 02:14 PM.

  86. #86
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Thanks

    It just the totals thats left to be sorted now!!!!! Yea almost done

  87. #87
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    I have made a small change the code i just posted. Please try and let me know.

  88. #88
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    It gives me a compile error syntax error in the following line

    i).value not like "grand Total" then

  89. #89
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Sorry a typo at my end. Try the code again please.

  90. #90
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Cool works now , but i need the average for column G

  91. #91
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Edited the code again. Please try it and let me know.

  92. #92
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  93. #93
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    No problem at all. Am glad it worked.

  94. #94
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Sorry just noticed my dates have changed to numbers????

  95. #95
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Ok, we will need to separately format the columns with numbers and the columns with dates.

  96. #96
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Only column A has dates

  97. #97
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    I have updated the code. You can try now.

  98. #98
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Works great thanks so much for your help!!!!

  99. #99
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    You are welcome. Thanks for the rep.

  100. #100
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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?

  101. #101
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Try this code and let me know -
    Please Login or Register  to view this content.

  102. #102
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi

    Sorry I have been on leave. The new macro still has the same issue - no idea why But I need to fix it!!!!

  103. #103
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi

    This is the outcome of running the macro.
    Attached Files Attached Files

  104. #104
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    This is the code I am using as we made a few column changes.

    Please Login or Register  to view this content.

  105. #105
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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?

  106. #106
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  107. #107
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Try this updated code -
    Please Login or Register  to view this content.

  108. #108
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  109. #109
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  110. #110
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    I manually added the columns and the answers are incorrect so something is not right.

  111. #111
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Did you manage to look at this one. Should I maybe start a new post with this query?

  112. #112
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Sorry. Let me look into this again. There is something not right with the calculations.

  113. #113
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  114. #114
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  115. #115
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Ok, should i use the same file that you had uploaded earlier? Are there any changes to the columns that need the calculations?

  116. #116
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi

    The columns for the calculations have stayed the same so you can use the previously uploaded file.

    Thanks

  117. #117
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Try this updated code - Had to make some adjustments for it to work right -

    Please Login or Register  to view this content.

  118. #118
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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
    Attached Files Attached Files

  119. #119
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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?

  120. #120
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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
    Attached Files Attached Files

  121. #121
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    See the attached - i ran the macro and there is no difference. My macro run is tab 1, yours is tab 2.
    Attached Files Attached Files

  122. #122
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  123. #123
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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?

  124. #124
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.

  125. #125
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Tried running it again and it still does the same . I have no idea what I'm doing wrong

  126. #126
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  127. #127
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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

  128. #128
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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.
    Attached Files Attached Files

  129. #129
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  130. #130
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  131. #131
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    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
    Attached Files Attached Files

  132. #132
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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.

  133. #133
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    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?

  134. #134
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi

    Columns D, F, G and J formula works correctly. The issue is from column L onwards

  135. #135
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Try this updated code -
    Please Login or Register  to view this content.

  136. #136
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Adding columns to a macro

    Hi
    Thanks it seems to be working fine now. Thanks for all your help. I have learnt alot working with you.

    Terri

  137. #137
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Adding columns to a macro

    Am glad its solved now. I learnt a lot too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1