I am currently working on a macro that formats a system generated report by
color coordinating accounts to prioritize what should be worked on first. I
am running into a couple of problems that could be solved if i knew how to do
the following: I would like help writting code that would check to see if an
account number and credit *i will give an example of what i mean* have
appeared before. I would attach a section of the report, but i dont know how
because once i paste, it is useless information without cells, so i will do
my best to explain.
A B C D
E F
Comments Bank PrimaryAccntNo OpenDate AvgBal TranType
nh - balance 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
D
46 123123123 3/06/07 $900.00
C
46 123123123 3/06/07 $900.00
D

There will ALWAYS be at least one C in the TranType column and at least one
D. There can be multiple C's and D's (credits (deposits) and debits
(checks)) for each account number. What i need is a macro that will
determine if a credit has appeared for the same account before, and if so, it
will take all deposited item amounts *not shown due to space constraints* and
add them together, compare that total with average that can be found in each
line, and then if certin conditions are met (in this macro, if the average
balance is less than two times the combined total of deposited items than one
type of comment is placed in column A corrisponding to the first line
containing the primaryaccount number being worked [nh - balance], and a
different comment in each line following the first one[ w ] *i have given an
example of what i want above*). I really hope someone can help me. If i
have been too vague or if you need more info, please, let me know and i will
provide it. Thanks.

Hi, you should write a do-while-loop which should iterate from the row that
contains data until there are no more data in the column (TranType). Within
the loop try doing the following,
1. Check if the TranType is C if so, X = X + Current Value of Avg. Bal.
2. Check if the TranType is D if so, Y = Y + Current Value of Avg. Bal.

It is advised that you ask for technical questions in this forum. Your job
specific queries may not be answered by all. You should try improvising on
the above solution.

"edluver" wrote:

I am currently working on a macro that formats a system generated report by
color coordinating accounts to prioritize what should be worked on first. I
am running into a couple of problems that could be solved if i knew how to do
the following: I would like help writting code that would check to see if an
account number and credit *i will give an example of what i mean* have
appeared before. I would attach a section of the report, but i dont know how
because once i paste, it is useless information without cells, so i will do
my best to explain.
A B C D
E F
Comments Bank PrimaryAccntNo OpenDate AvgBal TranType
nh - balance 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
D
46 123123123 3/06/07 $900.00
C
46 123123123 3/06/07 $900.00
D

There will ALWAYS be at least one C in the TranType column and at least one
D. There can be multiple C's and D's (credits (deposits) and debits
(checks)) for each account number. What i need is a macro that will
determine if a credit has appeared for the same account before, and if so, it
will take all deposited item amounts *not shown due to space constraints* and
add them together, compare that total with average that can be found in each
line, and then if certin conditions are met (in this macro, if the average
balance is less than two times the combined total of deposited items than one
type of comment is placed in column A corrisponding to the first line
containing the primaryaccount number being worked [nh - balance], and a
different comment in each line following the first one[ w ] *i have given an
example of what i want above*). I really hope someone can help me. If i
have been too vague or if you need more info, please, let me know and i will
provide it. Thanks.

Hi, you should write a do-while-loop which should iterate from the row that
contains data until there are no more data in the column (TranType). Within
the loop try doing the following,
1. Check if the TranType is C if so, X = X + Current Value of Avg. Bal.
2. Check if the TranType is D if so, Y = Y + Current Value of Avg. Bal.

It is advised that you ask for technical questions in this forum. Your job
specific queries may not be answered by all. You should try improvising on
the above solution.

"edluver" wrote:

I am currently working on a macro that formats a system generated report by
color coordinating accounts to prioritize what should be worked on first. I
am running into a couple of problems that could be solved if i knew how to do
the following: I would like help writting code that would check to see if an
account number and credit *i will give an example of what i mean* have
appeared before. I would attach a section of the report, but i dont know how
because once i paste, it is useless information without cells, so i will do
my best to explain.
A B C D
E F
Comments Bank PrimaryAccntNo OpenDate AvgBal TranType
nh - balance 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
D
46 123123123 3/06/07 $900.00
C
46 123123123 3/06/07 $900.00
D

There will ALWAYS be at least one C in the TranType column and at least one
D. There can be multiple C's and D's (credits (deposits) and debits
(checks)) for each account number. What i need is a macro that will
determine if a credit has appeared for the same account before, and if so, it
will take all deposited item amounts *not shown due to space constraints* and
add them together, compare that total with average that can be found in each
line, and then if certin conditions are met (in this macro, if the average
balance is less than two times the combined total of deposited items than one
type of comment is placed in column A corrisponding to the first line
containing the primaryaccount number being worked [nh - balance], and a
different comment in each line following the first one[ w ] *i have given an
example of what i want above*). I really hope someone can help me. If i
have been too vague or if you need more info, please, let me know and i will
provide it. Thanks.

Hi, use the omnipresent GOOGLE !!!

"edluver" wrote:

Thanks Madhan, i appreciate the help. Is there a good reference i should
look into buying that would be helpful with situations like this that you
would endorse?

"Madhan" wrote:

Hi, you should write a do-while-loop which should iterate from the row that
contains data until there are no more data in the column (TranType). Within
the loop try doing the following,
1. Check if the TranType is C if so, X = X + Current Value of Avg. Bal.
2. Check if the TranType is D if so, Y = Y + Current Value of Avg. Bal.

It is advised that you ask for technical questions in this forum. Your job
specific queries may not be answered by all. You should try improvising on
the above solution.

"edluver" wrote:

I am currently working on a macro that formats a system generated report by
color coordinating accounts to prioritize what should be worked on first. I
am running into a couple of problems that could be solved if i knew how to do
the following: I would like help writting code that would check to see if an
account number and credit *i will give an example of what i mean* have
appeared before. I would attach a section of the report, but i dont know how
because once i paste, it is useless information without cells, so i will do
my best to explain.
A B C D
E F
Comments Bank PrimaryAccntNo OpenDate AvgBal TranType
nh - balance 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
D
46 123123123 3/06/07 $900.00
C
46 123123123 3/06/07 $900.00
D

There will ALWAYS be at least one C in the TranType column and at least one
D. There can be multiple C's and D's (credits (deposits) and debits
(checks)) for each account number. What i need is a macro that will
determine if a credit has appeared for the same account before, and if so, it
will take all deposited item amounts *not shown due to space constraints* and
add them together, compare that total with average that can be found in each
line, and then if certin conditions are met (in this macro, if the average
balance is less than two times the combined total of deposited items than one
type of comment is placed in column A corrisponding to the first line
containing the primaryaccount number being worked [nh - balance], and a
different comment in each line following the first one[ w ] *i have given an
example of what i want above*). I really hope someone can help me. If i
have been too vague or if you need more info, please, let me know and i will
provide it. Thanks.