I've been doing manual work that I have a hunch could be completed with a
relatively simple macro. . .can somebody tell me 1. if it's possible and 2.
point me in the right direction toward writing the macro?

The task is this: I have two separate worksheets with the same accounts on
them. For each account, I have to take the new information from one document
and use it to revise the totals of the second in one to three steps,
depending upon the current balances of the account within the second document.

For example, let's say one of the accounts on both worksheets is 11112. One
worksheet (worksheet A) has changes in 11112 over the past month. . .let's
say $100 total. My task is to update the account 11112 in the second
worksheet (worksheet B). My update takes anywhere between 1 and 3 steps
because each account has balances in different areas. For my example, let's
say 11112 has a $20 in area X, a $25 in area Y and $1000 in area Z.

I take the $100 update from Worksheet A, use up the $20 in area X, the $25
in area Y and put the balance ($55) in Area Z of Worksheet B. . .then move on
to the next account.

The pattern for each account is consistent--if there is a balance in area X
I use that up first, Y second and put the balance in Z.

This is an easy enough task, but mind-numbing when done thousands of times.
Would a macro be able to help me automate this task?

thanks

Hmm, sounds like payment reconciliation against invoices to me :)

You *can* do this with a macro.
Get references to each worksheet, and interate over worksheet A's values.
for each of them, locate the matching entry in worksheet b.
Get your change amount, and then apply to worksheet b.

Because it supports functions/subs, you can break down individual parts of
the task into small pieces. For instance, your allocation routine can work
with an amount, and a range object which refers to a 1 row by 3 column block
on the destination (B) sheet.
If you've never done any VBA with Excel before, it's a steep start, since
you have to be able to get your head around the object model, which is vital
to locate various things. On the other hand, it would be more interesting
than doing it by hand, particularly given that once it's finished, you have a
'run macro' canned solution.
--
Steve S


"Gradnama" wrote:

I've been doing manual work that I have a hunch could be completed with a
relatively simple macro. . .can somebody tell me 1. if it's possible and 2.
point me in the right direction toward writing the macro?

The task is this: I have two separate worksheets with the same accounts on
them. For each account, I have to take the new information from one document
and use it to revise the totals of the second in one to three steps,
depending upon the current balances of the account within the second document.

For example, let's say one of the accounts on both worksheets is 11112. One
worksheet (worksheet A) has changes in 11112 over the past month. . .let's
say $100 total. My task is to update the account 11112 in the second
worksheet (worksheet B). My update takes anywhere between 1 and 3 steps
because each account has balances in different areas. For my example, let's
say 11112 has a $20 in area X, a $25 in area Y and $1000 in area Z.

I take the $100 update from Worksheet A, use up the $20 in area X, the $25
in area Y and put the balance ($55) in Area Z of Worksheet B. . .then move on
to the next account.

The pattern for each account is consistent--if there is a balance in area X
I use that up first, Y second and put the balance in Z.

This is an easy enough task, but mind-numbing when done thousands of times.
Would a macro be able to help me automate this task?

thanks

Hi, Steve
How to launch a VBA macro in excel? Thanks.

Thanks Steve. While your post gave me hope that it can be done,
unfortunately it also gave me a benchmark of where I need to be--I understood
very little of what you wrote. I guess I'll start trying to learn VBA!

Thanks for your help. If you have any great suggestions of the "best ways"
to learn VBA, please feel free to let me know.



"SteveS" wrote:

Hmm, sounds like payment reconciliation against invoices to me :)

You *can* do this with a macro.
Get references to each worksheet, and interate over worksheet A's values.
for each of them, locate the matching entry in worksheet b.
Get your change amount, and then apply to worksheet b.

Because it supports functions/subs, you can break down individual parts of
the task into small pieces. For instance, your allocation routine can work
with an amount, and a range object which refers to a 1 row by 3 column block
on the destination (B) sheet.
If you've never done any VBA with Excel before, it's a steep start, since
you have to be able to get your head around the object model, which is vital
to locate various things. On the other hand, it would be more interesting
than doing it by hand, particularly given that once it's finished, you have a
'run macro' canned solution.
--
Steve S


"Gradnama" wrote:

I've been doing manual work that I have a hunch could be completed with a
relatively simple macro. . .can somebody tell me 1. if it's possible and 2.
point me in the right direction toward writing the macro?

The task is this: I have two separate worksheets with the same accounts on
them. For each account, I have to take the new information from one document
and use it to revise the totals of the second in one to three steps,
depending upon the current balances of the account within the second document.

For example, let's say one of the accounts on both worksheets is 11112. One
worksheet (worksheet A) has changes in 11112 over the past month. . .let's
say $100 total. My task is to update the account 11112 in the second
worksheet (worksheet B). My update takes anywhere between 1 and 3 steps
because each account has balances in different areas. For my example, let's
say 11112 has a $20 in area X, a $25 in area Y and $1000 in area Z.

I take the $100 update from Worksheet A, use up the $20 in area X, the $25
in area Y and put the balance ($55) in Area Z of Worksheet B. . .then move on
to the next account.

The pattern for each account is consistent--if there is a balance in area X
I use that up first, Y second and put the balance in Z.

This is an easy enough task, but mind-numbing when done thousands of times.
Would a macro be able to help me automate this task?

thanks

On Fri, 14 Jul 2006 07:55:02 -0700, Gradnama
wrote:

Thanks Steve. While your post gave me hope that it can be done,
unfortunately it also gave me a benchmark of where I need to be--I understood
very little of what you wrote. I guess I'll start trying to learn VBA!

Thanks for your help. If you have any great suggestions of the "best ways"
to learn VBA, please feel free to let me know.


This will help you see and start to learn how macros are built:

Go to Tools | Macro | Record New Macro

Then work through what you would have the macro do by hand.

Don't try to do the whole thing at once, just do a few steps.

Then go to Tools | Macro | Stop Macro (this is probably called
something similar to this, I jdon't have it in front of me)

Then look at the macro that's been recorded.

Then post any questions you have and we can help you from there.

There is also a help file you can download from Microsoft on VBA.

For instance for Office 20000, go to microsoft.com and do a search for
VBA office 2000 help file and you can download it.

There are good books also, but try this stuff first. The help file
will give you an overview of how it works.

Greg