N
Neon520
Hi everyone,
I have a basic account tracking sheet created one per account in a folder
called TEST FOLDER.
The tracking sheet mainly contain the total charge, amount paid, method of
payment, the Month in which the payment is for, date of payment, and balance.
Most accounts charge is calculated by Fixed Days X Daily Fee, but some are
variable, which is calculated by Variable days X Daily Fee.
I would like to set up an automation macro so that it will COLLECT all
account who is not paid on time and place all info in a report format excel
spreadsheet.
I don't think it's practical to have the user goes into each account
spreadsheet and label it "LATE" in a particular Cell then run a Macro to
collect all the ones that are all labeled. I can't imagine doing this for a
large volume, even though I'm not dealing with a large volume right now.
Mainly, here is what I think is workable, even though it's still somewhat
prone to catch errors. Since each account spreadsheet will be updated by
putting the payment information and the Month that it's paid for, let's say
Jan payment, if we can filter those account spreadsheets that doesn't have
"Jan payment" on them, we can safely assume that those didn't pay yet, and
therefore are LATE.
First of all, let's call spreadsheet with account summary as <ACCOUNT> and
the spreadsheet that collect all late paying account as <LATE>
In <ACCOUNT>, here are the cells:
[MONTH] - specify month of payment.
[VARIABLE DAYS CHECKBOX] - Y/N
[DAYS] - if VARIABLE is Y, then there should be amount of Variable days here.
[DAILY FEE] - the daily in which is used to multiply with DAYS of the month
to come up with the charge.
Coding:
If [MONTH] in <ACCOUNT> doesn't match/exist in <LATE> AND [VARIABLE DAYS]
is N
Then copy [ACCOUNT #] in <ACCOUNT> to A2 in <LATE>
If [ACCOUNT #] doesn't exist
Then copy {FILE NAME} of that account to A2 instead (at least file
name give some clues, so the A1 won't be blank)
End if
copy the [DAILY FEE] in <ACCOUNT> to B2 in <LATE>
copy the [DAYS] in A1 in <LATE> to C2 in <LATE> (row one is the heading
row)
Else if
If [MONTH] in <ACCOUNT> doesn't match/exist in <LATE> AND [VARIABLE DAYS]
is Y
Then copy [ACCOUNT #] in <ACCOUNT> to A3 in <LATE>
If [ACCOUNT #] doesn't exist
Then copy {FILE NAME} of that account to A3 instead (at least file
name give some clues, so the A1 won't be blank)
End if
copy the [DAILY FEE] in <ACCOUNT> to B3 in <LATE>
copy the [DAYS] in AA1 in <ACCOUNT> to C3 in <LATE> (because VARIABLE
DAYS is Y so DAYS need to be copied from <ACCOUNT> instead.)
End if
Please keep in mind that the copy and paste is "PasteSpecial" only because
there will be built-in formula in <LATE> to calculate the Late Fee for each
account. (maybe it's D2 = B2 x C2)
Hopefully the Logic that I have here is correct. I never do any coding in
VBA before. Can someone help me code this in Excel VBA please?
If there is a better way to Reconcile all of the Late Fee Scenario I
mentioned about, I'm gladly open to any suggestion.
HEEELPP!!!!
Neon520
I have a basic account tracking sheet created one per account in a folder
called TEST FOLDER.
The tracking sheet mainly contain the total charge, amount paid, method of
payment, the Month in which the payment is for, date of payment, and balance.
Most accounts charge is calculated by Fixed Days X Daily Fee, but some are
variable, which is calculated by Variable days X Daily Fee.
I would like to set up an automation macro so that it will COLLECT all
account who is not paid on time and place all info in a report format excel
spreadsheet.
I don't think it's practical to have the user goes into each account
spreadsheet and label it "LATE" in a particular Cell then run a Macro to
collect all the ones that are all labeled. I can't imagine doing this for a
large volume, even though I'm not dealing with a large volume right now.
Mainly, here is what I think is workable, even though it's still somewhat
prone to catch errors. Since each account spreadsheet will be updated by
putting the payment information and the Month that it's paid for, let's say
Jan payment, if we can filter those account spreadsheets that doesn't have
"Jan payment" on them, we can safely assume that those didn't pay yet, and
therefore are LATE.
First of all, let's call spreadsheet with account summary as <ACCOUNT> and
the spreadsheet that collect all late paying account as <LATE>
In <ACCOUNT>, here are the cells:
[MONTH] - specify month of payment.
[VARIABLE DAYS CHECKBOX] - Y/N
[DAYS] - if VARIABLE is Y, then there should be amount of Variable days here.
[DAILY FEE] - the daily in which is used to multiply with DAYS of the month
to come up with the charge.
Coding:
If [MONTH] in <ACCOUNT> doesn't match/exist in <LATE> AND [VARIABLE DAYS]
is N
Then copy [ACCOUNT #] in <ACCOUNT> to A2 in <LATE>
If [ACCOUNT #] doesn't exist
Then copy {FILE NAME} of that account to A2 instead (at least file
name give some clues, so the A1 won't be blank)
End if
copy the [DAILY FEE] in <ACCOUNT> to B2 in <LATE>
copy the [DAYS] in A1 in <LATE> to C2 in <LATE> (row one is the heading
row)
Else if
If [MONTH] in <ACCOUNT> doesn't match/exist in <LATE> AND [VARIABLE DAYS]
is Y
Then copy [ACCOUNT #] in <ACCOUNT> to A3 in <LATE>
If [ACCOUNT #] doesn't exist
Then copy {FILE NAME} of that account to A3 instead (at least file
name give some clues, so the A1 won't be blank)
End if
copy the [DAILY FEE] in <ACCOUNT> to B3 in <LATE>
copy the [DAYS] in AA1 in <ACCOUNT> to C3 in <LATE> (because VARIABLE
DAYS is Y so DAYS need to be copied from <ACCOUNT> instead.)
End if
Please keep in mind that the copy and paste is "PasteSpecial" only because
there will be built-in formula in <LATE> to calculate the Late Fee for each
account. (maybe it's D2 = B2 x C2)
Hopefully the Logic that I have here is correct. I never do any coding in
VBA before. Can someone help me code this in Excel VBA please?
If there is a better way to Reconcile all of the Late Fee Scenario I
mentioned about, I'm gladly open to any suggestion.
HEEELPP!!!!
Neon520