Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Macro to sum lines-HELP PLEASE!!!!!!!!!
Hi -
I have been trying to make a macro that will automatically add up all of my project debits and credits. I have been unable to get this accomplished without having a run time error if there aren't any projects, or any of the projects I have specified. Project numbers look like this: F05GX35509 There are thousands of projects, but they are categorized by the first 3 digits. The first 3 digits could be F05, M04, etc....etc....and they will change every fiscal year. Here is what I would like to be able to do.... 1. Find the end of the data 2. Insert a formula to a)find the first 3 digits in a 10 digit number from column c b) sum up the debits in column e and the credits in column f and place them at the end of the data sequence respectively, and c) show the difference between credits and debits in column G 3. Label column B (to the left) of the formula what it is...i.e. if it sums up F05..then I want it to label F05...etc... Can anyone help with this???? I am not very good at writing code, only recording it while in excel, and I cannot figure out how to make this look at the change, but I know it can be done...I am not going to post my code because I don't think the format is correct at all..... Thanks in advance....Sara |
#2
|
|||
|
|||
Sara: Why don't you insert a new column to the left of your project numbers.
In it, put a formula like this: =left(b2,3) and that will return the first 3 digits. Now, using Data-Subtotals to get your totals. No macro needed, turn the subtotals off when you're done. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Frantic Excel-er" wrote in message ... Hi - I have been trying to make a macro that will automatically add up all of my project debits and credits. I have been unable to get this accomplished without having a run time error if there aren't any projects, or any of the projects I have specified. Project numbers look like this: F05GX35509 There are thousands of projects, but they are categorized by the first 3 digits. The first 3 digits could be F05, M04, etc....etc....and they will change every fiscal year. Here is what I would like to be able to do.... 1. Find the end of the data 2. Insert a formula to a)find the first 3 digits in a 10 digit number from column c b) sum up the debits in column e and the credits in column f and place them at the end of the data sequence respectively, and c) show the difference between credits and debits in column G 3. Label column B (to the left) of the formula what it is...i.e. if it sums up F05..then I want it to label F05...etc... Can anyone help with this???? I am not very good at writing code, only recording it while in excel, and I cannot figure out how to make this look at the change, but I know it can be done...I am not going to post my code because I don't think the format is correct at all..... Thanks in advance....Sara |
#3
|
|||
|
|||
I would like for this to be a macro because it is done about 30 times a
day....I am trying to automate a billing process, and this is just one facet of it....I have already written 2 other macros that have decreased time spent on it.... Other than that, I have to document everything that I do, so I would want the steps to show. Thanks for your post, though....it will definitely be handy if no one responds on how to write the macro. "Anne Troy" wrote: Sara: Why don't you insert a new column to the left of your project numbers. In it, put a formula like this: =left(b2,3) and that will return the first 3 digits. Now, using Data-Subtotals to get your totals. No macro needed, turn the subtotals off when you're done. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Frantic Excel-er" wrote in message ... Hi - I have been trying to make a macro that will automatically add up all of my project debits and credits. I have been unable to get this accomplished without having a run time error if there aren't any projects, or any of the projects I have specified. Project numbers look like this: F05GX35509 There are thousands of projects, but they are categorized by the first 3 digits. The first 3 digits could be F05, M04, etc....etc....and they will change every fiscal year. Here is what I would like to be able to do.... 1. Find the end of the data 2. Insert a formula to a)find the first 3 digits in a 10 digit number from column c b) sum up the debits in column e and the credits in column f and place them at the end of the data sequence respectively, and c) show the difference between credits and debits in column G 3. Label column B (to the left) of the formula what it is...i.e. if it sums up F05..then I want it to label F05...etc... Can anyone help with this???? I am not very good at writing code, only recording it while in excel, and I cannot figure out how to make this look at the change, but I know it can be done...I am not going to post my code because I don't think the format is correct at all..... Thanks in advance....Sara |
#4
|
|||
|
|||
I agree with the poster, use an extra column and the subtotal command.
If it's done 30 times a day, you can still use a macro to automate that part (ie record adding the subtotals)... As far as documenting what you do, document the subtotal command. speaking as someone who's had to maintain someone else's code that does all the subtotalling with loops and abstract variable names (giraffe, katie, x1, x2, etc) I would MUCH prefer that someone take advantage of functionality available out of the box than to reinvent the wheel.. HTH "Frantic Excel-er" wrote: I would like for this to be a macro because it is done about 30 times a day....I am trying to automate a billing process, and this is just one facet of it....I have already written 2 other macros that have decreased time spent on it.... Other than that, I have to document everything that I do, so I would want the steps to show. Thanks for your post, though....it will definitely be handy if no one responds on how to write the macro. "Anne Troy" wrote: Sara: Why don't you insert a new column to the left of your project numbers. In it, put a formula like this: =left(b2,3) and that will return the first 3 digits. Now, using Data-Subtotals to get your totals. No macro needed, turn the subtotals off when you're done. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Frantic Excel-er" wrote in message ... Hi - I have been trying to make a macro that will automatically add up all of my project debits and credits. I have been unable to get this accomplished without having a run time error if there aren't any projects, or any of the projects I have specified. Project numbers look like this: F05GX35509 There are thousands of projects, but they are categorized by the first 3 digits. The first 3 digits could be F05, M04, etc....etc....and they will change every fiscal year. Here is what I would like to be able to do.... 1. Find the end of the data 2. Insert a formula to a)find the first 3 digits in a 10 digit number from column c b) sum up the debits in column e and the credits in column f and place them at the end of the data sequence respectively, and c) show the difference between credits and debits in column G 3. Label column B (to the left) of the formula what it is...i.e. if it sums up F05..then I want it to label F05...etc... Can anyone help with this???? I am not very good at writing code, only recording it while in excel, and I cannot figure out how to make this look at the change, but I know it can be done...I am not going to post my code because I don't think the format is correct at all..... Thanks in advance....Sara |
#5
|
|||
|
|||
I have tried to do what Anne has suggested, but it won't work in my
spreadsheet....the data is imported from a .asc file, and when I add the column and place the formula there, it only shows the formula, not the results. I have tried to change the format to no avail... Also, there are no column headings in the spreadsheet...I would also have to add those to get the spreadsheet to work....Any more suggestions????? "TomHinkle" wrote: I agree with the poster, use an extra column and the subtotal command. If it's done 30 times a day, you can still use a macro to automate that part (ie record adding the subtotals)... As far as documenting what you do, document the subtotal command. speaking as someone who's had to maintain someone else's code that does all the subtotalling with loops and abstract variable names (giraffe, katie, x1, x2, etc) I would MUCH prefer that someone take advantage of functionality available out of the box than to reinvent the wheel.. HTH "Frantic Excel-er" wrote: I would like for this to be a macro because it is done about 30 times a day....I am trying to automate a billing process, and this is just one facet of it....I have already written 2 other macros that have decreased time spent on it.... Other than that, I have to document everything that I do, so I would want the steps to show. Thanks for your post, though....it will definitely be handy if no one responds on how to write the macro. "Anne Troy" wrote: Sara: Why don't you insert a new column to the left of your project numbers. In it, put a formula like this: =left(b2,3) and that will return the first 3 digits. Now, using Data-Subtotals to get your totals. No macro needed, turn the subtotals off when you're done. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Frantic Excel-er" wrote in message ... Hi - I have been trying to make a macro that will automatically add up all of my project debits and credits. I have been unable to get this accomplished without having a run time error if there aren't any projects, or any of the projects I have specified. Project numbers look like this: F05GX35509 There are thousands of projects, but they are categorized by the first 3 digits. The first 3 digits could be F05, M04, etc....etc....and they will change every fiscal year. Here is what I would like to be able to do.... 1. Find the end of the data 2. Insert a formula to a)find the first 3 digits in a 10 digit number from column c b) sum up the debits in column e and the credits in column f and place them at the end of the data sequence respectively, and c) show the difference between credits and debits in column G 3. Label column B (to the left) of the formula what it is...i.e. if it sums up F05..then I want it to label F05...etc... Can anyone help with this???? I am not very good at writing code, only recording it while in excel, and I cannot figure out how to make this look at the change, but I know it can be done...I am not going to post my code because I don't think the format is correct at all..... Thanks in advance....Sara |
#6
|
|||
|
|||
Frantic: Add the column. Select the column and choose Edit--Clear--All.
Save your file, THEN do the formula. THEN add your headings. That should work for you. To bring it all together, save your macro file as an XLA file that even goes and opens your ascii file for you. Then, just replace the ascii file each day. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Frantic Excel-er" wrote in message ... I have tried to do what Anne has suggested, but it won't work in my spreadsheet....the data is imported from a .asc file, and when I add the column and place the formula there, it only shows the formula, not the results. I have tried to change the format to no avail... Also, there are no column headings in the spreadsheet...I would also have to add those to get the spreadsheet to work....Any more suggestions????? "TomHinkle" wrote: I agree with the poster, use an extra column and the subtotal command. If it's done 30 times a day, you can still use a macro to automate that part (ie record adding the subtotals)... As far as documenting what you do, document the subtotal command. speaking as someone who's had to maintain someone else's code that does all the subtotalling with loops and abstract variable names (giraffe, katie, x1, x2, etc) I would MUCH prefer that someone take advantage of functionality available out of the box than to reinvent the wheel.. HTH "Frantic Excel-er" wrote: I would like for this to be a macro because it is done about 30 times a day....I am trying to automate a billing process, and this is just one facet of it....I have already written 2 other macros that have decreased time spent on it.... Other than that, I have to document everything that I do, so I would want the steps to show. Thanks for your post, though....it will definitely be handy if no one responds on how to write the macro. "Anne Troy" wrote: Sara: Why don't you insert a new column to the left of your project numbers. In it, put a formula like this: =left(b2,3) and that will return the first 3 digits. Now, using Data-Subtotals to get your totals. No macro needed, turn the subtotals off when you're done. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Frantic Excel-er" wrote in message ... Hi - I have been trying to make a macro that will automatically add up all of my project debits and credits. I have been unable to get this accomplished without having a run time error if there aren't any projects, or any of the projects I have specified. Project numbers look like this: F05GX35509 There are thousands of projects, but they are categorized by the first 3 digits. The first 3 digits could be F05, M04, etc....etc....and they will change every fiscal year. Here is what I would like to be able to do.... 1. Find the end of the data 2. Insert a formula to a)find the first 3 digits in a 10 digit number from column c b) sum up the debits in column e and the credits in column f and place them at the end of the data sequence respectively, and c) show the difference between credits and debits in column G 3. Label column B (to the left) of the formula what it is...i.e. if it sums up F05..then I want it to label F05...etc... Can anyone help with this???? I am not very good at writing code, only recording it while in excel, and I cannot figure out how to make this look at the change, but I know it can be done...I am not going to post my code because I don't think the format is correct at all..... Thanks in advance....Sara |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
soft-coding lines in a macro | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) |