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

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

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

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

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

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

I got the formula to show the result, and I added the column headings....but
I am up against new problems.

1...I am trying to write this in code.....the column headings have to be
above my project lines, which are separated by a blank row from non-project
lines - this separation happens on different rows in each file. When I
record the macro, it tries to put the column headings in a specific
row...which I need to be relative
2. For simplicity I want to just use the column titles (a,b,c,d...etc) as
the column headings - is there an easy way to write this in code, other than
saying Range(A17).Select???
3. The autofill feature for the formula also wants to be specific, but I
need that to go to the end of the data lines.....when I record that, even in
relative, it is still specifying a specific cell.....

I really appreciate your help...and I don't mean this in a rude sense at
all....but these questions are why I asked how the code would look......I
don't know enough of how to write it fix these kinds of questions.

Thanks again...Sara

"Anne Troy" wrote:

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