track WU balance - continuation from last question
gowflow, some changes to how I first presented this.
Call the new sheet: 'WU Balance' if you want.
WU balance needs:
- Col A: 'Date' - the date the function is run by clicking a button - each click produces today's date and new row
- Col B: 'Total Confirmed Received' - ongoing sum of Col i's (from WU-Staging-FBME) starting from 10/24/11 - a chosen starting date in Col B (from WU-Staging-FBME) -
I'm trying to pick a starting date because right now, he still owes us money, but I'm not sure how much. We are not sending him anymore pickup requests until he zeroes out his account with us (owes us nothing) and there is one pickup request from a WUOct24-11 he has not picked up. When he does pick this one up, it will be the first addition to the balance from the starting point of zero. Confusing?
- Col C: 'WU Wire Received' - the imported email (example attached).The amount sent + $5 (bank wire fee)
- Col D: 'Josef's Commission' - calculation: 5% of total of Col B (the total of Col i's - the confirmation of Josef's pickups)
- Col E: 'Balance' - (Col B - (Col C + Col D))
Only part I'm not so sure on is how we work the date. Let me know if this makes sense. Thanks again, gowflow.
WU-Payment-Received.msg
Call the new sheet: 'WU Balance' if you want.
WU balance needs:
- Col A: 'Date' - the date the function is run by clicking a button - each click produces today's date and new row
- Col B: 'Total Confirmed Received' - ongoing sum of Col i's (from WU-Staging-FBME) starting from 10/24/11 - a chosen starting date in Col B (from WU-Staging-FBME) -
I'm trying to pick a starting date because right now, he still owes us money, but I'm not sure how much. We are not sending him anymore pickup requests until he zeroes out his account with us (owes us nothing) and there is one pickup request from a WUOct24-11 he has not picked up. When he does pick this one up, it will be the first addition to the balance from the starting point of zero. Confusing?
- Col C: 'WU Wire Received' - the imported email (example attached).The amount sent + $5 (bank wire fee)
- Col D: 'Josef's Commission' - calculation: 5% of total of Col B (the total of Col i's - the confirmation of Josef's pickups)
- Col E: 'Balance' - (Col B - (Col C + Col D))
Only part I'm not so sure on is how we work the date. Let me know if this makes sense. Thanks again, gowflow.
WU-Payment-Received.msg
An other question
I suggest when we sent a file to our freind to start updating the WU Balance with what is going out and see if we can then match buy what he send us this way you will keep track of what your sending and in a column you can have the diffrenc between what you send and what he claim to have recived so you see the diffrence
something like htis just throwing ideas for you does it make sence?
gowflow
I suggest when we sent a file to our freind to start updating the WU Balance with what is going out and see if we can then match buy what he send us this way you will keep track of what your sending and in a column you can have the diffrenc between what you send and what he claim to have recived so you see the diffrence
something like htis just throwing ideas for you does it make sence?
gowflow
ASKER
If I have to manually input, then I guess I will. I'm all for automation as you know. But maybe for now it is best to do it manually just to see what, if anything, needs tweaking.
Your idea makes sense. That is probably best.
Your idea makes sense. That is probably best.
I meant manually only for 1 column hte content of the email !! don't get me wrong the rest is not problem mabe when we go deep in the routine will find better handling. So if my idea is make sence can you pls let me know how you wnat the columns ... like you did in the beg of this question incorportate the new idea
gowflow
gowflow
ASKER
Attached is an example - will most likely need some tweaking. Needs cumulative.
And you will also see WU Balance on the Visa workbook you have, that someone else did, but it didn't work because I wasn't getting the same balance Josef had. Not sure why. But this time, the goal is for us to start at 0 balance and maybe that will do it. If not, then we'll have to start off with some balance on their account which I will know before too long - I hope. WU-Balance-Example.xls
And you will also see WU Balance on the Visa workbook you have, that someone else did, but it didn't work because I wasn't getting the same balance Josef had. Not sure why. But this time, the goal is for us to start at 0 balance and maybe that will do it. If not, then we'll have to start off with some balance on their account which I will know before too long - I hope. WU-Balance-Example.xls
yes just found the one in hte file which is diffrent from the one you attached which one you want to use ? the one in the file seem to have buttons also maybe better to work on the one in the file ? You tell me
gowflow
gowflow
ASKER
Whichever one you want is fine - however, the old one produces an error when I select the drop down date box: Sub or Function not defined, with this highlighted in yellow: Private Sub Worksheet_SelectionChange(
And now that I remember I think the old listed every single pickup request which I don't need. I just need the amounts from Col i listed.
Let's use the one I just sent you, and like I said it may need some tweaking once I try it out a few times.
Thank you.
And now that I remember I think the old listed every single pickup request which I don't need. I just need the amounts from Col i listed.
Let's use the one I just sent you, and like I said it may need some tweaking once I try it out a few times.
Thank you.
I am reading now the post for this question and looking at the file you attached and I am a bit confused as to how you want to record data in this file
Do you want to record the part that concern your company (when sending the file to Joseph from the file we generate in the yellow button and match the data with the file that you receive when Joseph sends his file ?
OR
You need to update the whole WU Balance sheet based only on hte file that Joseph sends ???
If you do hte latter then you will surely end up with diffences, if you do the first option then you will at least keep track of what you are sending and try to match what he is sending to you to see the balance.
If it is the case then I have a question
In the file he sends you he sometimes hav ein 1 file several WUtoday's date and here in the WU Balance the data will be broken by each WUDate so my question is:
If say today for a certain WUDate he sends you 10 items from originally 20 for that day and we update WU Balance and in couple of days he sends the 10 remaining for that same date then I guess will need to update that same row always adding the previous data that exist so the WUDate can close and get to balance 0 !!! if it is the case then
You need the field that shows his amount not to include any comission or charges so it can match and add up to zeor
Don't know if I made myself clear but I hope you got the essence.
gowflow
Do you want to record the part that concern your company (when sending the file to Joseph from the file we generate in the yellow button and match the data with the file that you receive when Joseph sends his file ?
OR
You need to update the whole WU Balance sheet based only on hte file that Joseph sends ???
If you do hte latter then you will surely end up with diffences, if you do the first option then you will at least keep track of what you are sending and try to match what he is sending to you to see the balance.
If it is the case then I have a question
In the file he sends you he sometimes hav ein 1 file several WUtoday's date and here in the WU Balance the data will be broken by each WUDate so my question is:
If say today for a certain WUDate he sends you 10 items from originally 20 for that day and we update WU Balance and in couple of days he sends the 10 remaining for that same date then I guess will need to update that same row always adding the previous data that exist so the WUDate can close and get to balance 0 !!! if it is the case then
You need the field that shows his amount not to include any comission or charges so it can match and add up to zeor
Don't know if I made myself clear but I hope you got the essence.
gowflow
ASKER
"You need to update the whole WU Balance sheet based only on hte file that Joseph sends ???"
Yes, because what matters is what was REALLY sent to him and what he REALLY picks up.
The WU-Staging-FBME keeps track of the differences between what I send him and what he actually picks up.
WU Balance will keep track of what his outstanding balance with us. The balance is the difference between what he says he picked up (Col H summed in Col i) and what he wires to us - adding his commission. Most of the time it will be a negative balance (meaning he owes us money) because he will always sends wires (payments) to us after he's picked up a number of batches.
From what I understand, your last paragraph is correct. He will pick up parts of batches on one day and then the rest of it on other days. This is the tricky part of it.
The commissions + his email notification of a wire sent back to us should = Col i's totals. In other words, he has collected everything indicated in Col i, takes out his commission and sends us the rest.
Then, later (which you already worked on) we take out our loading fee (7.5%) from the Col i amount, after it's converted to Euros. So in effect, we get 7% loading fee and Josef gets .5% for his pick up service.
Yes, because what matters is what was REALLY sent to him and what he REALLY picks up.
The WU-Staging-FBME keeps track of the differences between what I send him and what he actually picks up.
WU Balance will keep track of what his outstanding balance with us. The balance is the difference between what he says he picked up (Col H summed in Col i) and what he wires to us - adding his commission. Most of the time it will be a negative balance (meaning he owes us money) because he will always sends wires (payments) to us after he's picked up a number of batches.
From what I understand, your last paragraph is correct. He will pick up parts of batches on one day and then the rest of it on other days. This is the tricky part of it.
The commissions + his email notification of a wire sent back to us should = Col i's totals. In other words, he has collected everything indicated in Col i, takes out his commission and sends us the rest.
Then, later (which you already worked on) we take out our loading fee (7.5%) from the Col i amount, after it's converted to Euros. So in effect, we get 7% loading fee and Josef gets .5% for his pick up service.
well I am more confused now let me tell you the fear I have
If you rely strictly on his file say he remove from hte file you send him 2 or 3 items that he will never send you then there will be no way for you to track these missing items. I am taking the extreeme here. Whereas if you rely on your file and match what he has send you then you will always be on top.
The way I see it is to group by WUdate the amounts in the file you send him plug the appropriate columns and then from the file we receive from his also group by WUdate and plug the necessary columns for the data he reported (even if this means that you could end up with 2 similar column 1 from your side and the other from his side this would be the check that all data is there and he did not delete a line by mistake which could happen ... I would see say col A to say D from your side and E to say G from his side per row (D of G are just srbitrary so you understand the concept)
Let me know your houghts
gowflow
If you rely strictly on his file say he remove from hte file you send him 2 or 3 items that he will never send you then there will be no way for you to track these missing items. I am taking the extreeme here. Whereas if you rely on your file and match what he has send you then you will always be on top.
The way I see it is to group by WUdate the amounts in the file you send him plug the appropriate columns and then from the file we receive from his also group by WUdate and plug the necessary columns for the data he reported (even if this means that you could end up with 2 similar column 1 from your side and the other from his side this would be the check that all data is there and he did not delete a line by mistake which could happen ... I would see say col A to say D from your side and E to say G from his side per row (D of G are just srbitrary so you understand the concept)
Let me know your houghts
gowflow
ASKER
Regarding your first paragraph.
It is a truth that not every pickup request in a batch will be picked up. Some have errors and need to be resent, therefore negating those pickup requests. Others will be picked up later (he can only pick up $7,000 max per day). That is why we can only balance our account with him from what he has actually picked up: Col i.
So for knowing our balance with him we don't care about what we sent him (Col G) only what he says he actually picked up (Col H summed in Col i). Don't use Col G in this equation. It only matters what he picked up or did not pick up.
Is this part clear? If it settles it let me know. Now for the second part of your question.....
It is a truth that not every pickup request in a batch will be picked up. Some have errors and need to be resent, therefore negating those pickup requests. Others will be picked up later (he can only pick up $7,000 max per day). That is why we can only balance our account with him from what he has actually picked up: Col i.
So for knowing our balance with him we don't care about what we sent him (Col G) only what he says he actually picked up (Col H summed in Col i). Don't use Col G in this equation. It only matters what he picked up or did not pick up.
Is this part clear? If it settles it let me know. Now for the second part of your question.....
ok fine at the end ur the boss ! I only developp. as I don't fully understand ur biz don't really know the impact.
go ahead with the second part
gowflow
go ahead with the second part
gowflow
ASKER
Not sure exactly what you are proposing with your second paragraph, but I'm not at all attached to the layout or how it's done. I just need to know what he owes us after each spreadsheet he sends me, of course deducting payments he sends and commissions he keeps.
ASKER
did you get my request on another previously answered and solved question? I have an addendum to it that I will submit a related question for. It is about adding a page to the HMF / MCR function you created. If not, let me know and will paste the link to it here.
no I deleted the previous question when you ask a new one I remove the old. Maybe its in my emails but let me get to work
so to summ it up I need to build WU Balance from only the file he sends right?
the only thing left is to know
if he send 1 day WUOct14-11 couple of items and I pull them in WU Balance on row say 1
and after couple of days I see in the file he send also some items with WUOct14-11 then whatever I have in the new batch should be added to the existing WUOct14-11 in WU Balance on row1 and not created as a new row RIGHT ???? this is the main part all the rest is trivial !
Based on your reply I will build the model
gowflow
so to summ it up I need to build WU Balance from only the file he sends right?
the only thing left is to know
if he send 1 day WUOct14-11 couple of items and I pull them in WU Balance on row say 1
and after couple of days I see in the file he send also some items with WUOct14-11 then whatever I have in the new batch should be added to the existing WUOct14-11 in WU Balance on row1 and not created as a new row RIGHT ???? this is the main part all the rest is trivial !
Based on your reply I will build the model
gowflow
ASKER
yes, from what he sends but that is pasted into Col H of my spreadsheet and summed in Col i
I would prefer to see the amounts as they come in, both in wires he sends and in pickups he's made. That way I can see the history of the money flow there and back.
I would prefer to see the amounts as they come in, both in wires he sends and in pickups he's made. That way I can see the history of the money flow there and back.
ASKER
since I told you about the other related question I posted, here it is. Let me know when you want me to create a question out of it.
------------------
Hi gowflow, I have to add another sheet that does the same thing you programmed the HMF Account page to do. The new sheet is Fi2. I copied the HMF Account page to create it.
Let me know if you get this and want to do it and I'll submit a related question.
It might be good to allow me (if at all possible) or tell me how to add sheets in the future that have the same functionality as HMF Account and for the MCR eForex pages.
Thank you.
------------------
Hi gowflow, I have to add another sheet that does the same thing you programmed the HMF Account page to do. The new sheet is Fi2. I copied the HMF Account page to create it.
Let me know if you get this and want to do it and I'll submit a related question.
It might be good to allow me (if at all possible) or tell me how to add sheets in the future that have the same functionality as HMF Account and for the MCR eForex pages.
Thank you.
pls lets stay with 1 issue at a time. I will concentrate on this now and once done will look at HMF !!! You think I am a martian that can digest many issues at once ??? Am a less than normal human that need time to digest 1 issue !!!
to go back to your 2nd previous post:
=============
yes, from what he sends but that is pasted into Col H of my spreadsheet and summed in Col i
I would prefer to see the amounts as they come in, both in wires he sends and in pickups he's made. That way I can see the history of the money flow there and back.
==============
Almost chineese to me !!! Like you don't want me to read from the file he send you I was thinking of incorporating the new routine to update WU-Balance-New (I called it) when you activate the blue button Import WU Confirmed Amounts and once this is done at the end to start updating WU-Balance-New. If all the info we need is in that file why do we need to go to WU-Staging-FBME ?
I see you have one line per WUOct14-11 (by date) a total so I would simply total col G and Col H from the file I am reading sorting it by WUdate and plugging it in WU-Balance-New with in hte appropriate columns and adding the comission and whatever balance etc ...
no ?
gowlfow
to go back to your 2nd previous post:
=============
yes, from what he sends but that is pasted into Col H of my spreadsheet and summed in Col i
I would prefer to see the amounts as they come in, both in wires he sends and in pickups he's made. That way I can see the history of the money flow there and back.
==============
Almost chineese to me !!! Like you don't want me to read from the file he send you I was thinking of incorporating the new routine to update WU-Balance-New (I called it) when you activate the blue button Import WU Confirmed Amounts and once this is done at the end to start updating WU-Balance-New. If all the info we need is in that file why do we need to go to WU-Staging-FBME ?
I see you have one line per WUOct14-11 (by date) a total so I would simply total col G and Col H from the file I am reading sorting it by WUdate and plugging it in WU-Balance-New with in hte appropriate columns and adding the comission and whatever balance etc ...
no ?
gowlfow
ASKER
Well, you don't need to read from his spreadsheet for this function because you are already doing that with that last code you created, aren't you? Maybe I'm not remembering correctly.
Only reason we need WU-Staging-FBME is to grab the value in Col i. That is the ONLY value we're interested in. We take Col i - the amount he's picked up for a batch and the sum of Col i's tell us how much of our money he has. You don't need Col G. Just the values of Col H, which is summed into a single cell in Col i.
Then, we take the Col i's subtract the emailed notification that they sent a wire (+ $5.00 per wire) and subtract his .5% commission from Col i and it should equal zero, but won't because he will always have more in hand than he sends back to us.
Am I making sense?
Only reason we need WU-Staging-FBME is to grab the value in Col i. That is the ONLY value we're interested in. We take Col i - the amount he's picked up for a batch and the sum of Col i's tell us how much of our money he has. You don't need Col G. Just the values of Col H, which is summed into a single cell in Col i.
Then, we take the Col i's subtract the emailed notification that they sent a wire (+ $5.00 per wire) and subtract his .5% commission from Col i and it should equal zero, but won't because he will always have more in hand than he sends back to us.
Am I making sense?
well my prolem is how t ofind the sum of Col I I need to report in WU-Balance-New ? If I remember well Col I only sees a value when a batch is complete is this what you want the reporting of all the complete batches and not all the content of the file he send you for the col he fills (Col H Confirmed Amounts ???)
I am totally confused. coz If I need to go thru WU-Staging-FBME dont really know where to start and what to look for and where to end andd how to group them by WUDate !!!
I actually just finished doing the routine based on the file he sends ! Would you like to try it ? the only issue I have is finding the date in Col A the rest make sense for me unless for you the titles means something else
That's what I have:
WU Balance File from Joseph
A Problem
B P
C G
D H
G = D*0.5%
H4 = G4+H3
I4 = E4+G4-D4
J4 = I4+J3
If it is not form his file you need to explain in details as an example how to fill the file taking in concideration what you have in WU-Staging-FBME and pls when you explain forget about your 5$ and the 5% and the commision and what he owes you and what is picked as all these are not relevant to me. I need to understand what to take from where in what sheet till where even if they are potatos I don't care as long as I understand the mecanics.
Capich ???
Sometimes I feel we don't communicate at all and this is where my blood presure hits the roof !!!
gowflow
gowflow
I am totally confused. coz If I need to go thru WU-Staging-FBME dont really know where to start and what to look for and where to end andd how to group them by WUDate !!!
I actually just finished doing the routine based on the file he sends ! Would you like to try it ? the only issue I have is finding the date in Col A the rest make sense for me unless for you the titles means something else
That's what I have:
WU Balance File from Joseph
A Problem
B P
C G
D H
G = D*0.5%
H4 = G4+H3
I4 = E4+G4-D4
J4 = I4+J3
If it is not form his file you need to explain in details as an example how to fill the file taking in concideration what you have in WU-Staging-FBME and pls when you explain forget about your 5$ and the 5% and the commision and what he owes you and what is picked as all these are not relevant to me. I need to understand what to take from where in what sheet till where even if they are potatos I don't care as long as I understand the mecanics.
Capich ???
Sometimes I feel we don't communicate at all and this is where my blood presure hits the roof !!!
gowflow
gowflow
Don't know if this is what you want but you can try this and let me know.
1) make a new copy of the latest Visa file
2) doubleclick module1 and delete Sub WUConfirmedAmounts
3) Copy the below code in mosule1 after any end sub
4) Save the workbook
5) Attach the sheet you have send me and rename it to "WU-Balance-New" so you can keep the other WU Balance sheet there.
6) save and exit the workbook
to try it you need to activate the Import WU Confirmed Amounts and it will update automatically WU-Balance-New. I suggest for trial keep only the header in row1 and delete all the rest of the data and import again the last file received from Joseph and see the results
let me know
gowflow
1) make a new copy of the latest Visa file
2) doubleclick module1 and delete Sub WUConfirmedAmounts
3) Copy the below code in mosule1 after any end sub
Sub ImportWUConfirmedAmts()
Dim WS As Worksheet
Dim WSWU As Worksheet
Dim WSWUBalance As Worksheet
Dim WB As Workbook
Dim I As Long, J As Long
Dim MaxRowWU As Long, MaxRowWUBalance As Long
Dim Rng As Range
Dim TConfirmed As Double, TColG As Double, TColH As Double
Dim WUFile As String, WUDate As String
Dim C
If MsgBox("Are you ready to Import WU Confirmed Amounts ?", vbQuestion + vbYesNo, "Import WU Confirmed Amounts") = vbYes Then
Do
WUFile = GFileName(gstFolderWesternUnion)
If WUFile = "" Then
If MsgBox("No file has been selected" & Chr(10) _
& "[OK] to continue and select a file." & Chr(10) _
& "[Cancel] to Exit." & Chr(10) & Chr(10) _
& "Please make a selection.", vbInformation + vbOKCancel, "Import WU Confirmed Amounts") = vbCancel Then
Exit Sub
End If
End If
Loop Until WUFile <> ""
Set WS = Sheets("WU-Staging-FBME")
Set WSWUBalance = Sheets("WU-Balance-New")
Set WB = Workbooks.Open(WUFile)
Set WSWU = ActiveSheet
MaxRowWU = WSWU.Rows(WSWU.Rows.Count).End(xlUp).Row
MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
For I = 3 To MaxRowWU
If WSWU.Cells(I, "H") <> "" Then
WS.UsedRange.AutoFilter 4, WSWU.Cells(I, "D")
WS.UsedRange.AutoFilter 3, Criteria1:=">=" & WSWU.Cells(I, "C"), Operator:=xlAnd, Criteria2:="<=" & WSWU.Cells(I, "C")
WS.UsedRange.AutoFilter 2, WSWU.Cells(I, "B")
Set Rng = WS.UsedRange.SpecialCells(xlCellTypeVisible)
For Each Row In Rng.EntireRow
If Not Row.Row = 1 Then
If WSWU.Cells(I, "A") = WS.Cells(Row.Row, "A") And WSWU.Cells(I, "E") = WS.Cells(Row.Row, "E") And WSWU.Cells(I, "F") = WS.Cells(Row.Row, "F") And WS.Cells(Row.Row, "H").Value = "" Then
WS.Cells(Row.Row, "H").Value = WSWU.Cells(I, "H").Value
TConfirmed = TConfirmed + WSWU.Cells(I, "H").Value
J = J + 1
Exit For
End If
End If
Next Row
End If
Next I
WS.ShowAllData
WS.AutoFilterMode = False
MsgBox ("Confirmed Amounts Updated successfully for " & J & " records totalling " & TConfirmed)
'Update WU-Balance-New
'>> 1 >> Sort WU on Col P
WSWU.Range("A3:P" & MaxRowWU).Sort Key1:=WSWU.Columns("P"), order1:=xlAscending, Header:=xlGuess
TColG = 0
TColH = 0
WUDate = WSWU.Cells(3, "P")
'>> 2 >> Add Col G and Col H for each WUDate
For I = 3 To MaxRowWU
If WSWU.Cells(I, "P") <> "" Then
If WSWU.Cells(I, "P") = WUDate Then
TColG = TColG + WSWU.Cells(I, "G")
TColH = TColH + WSWU.Cells(I, "H")
Else
'Lookfor WUDate in sheet 'WU-Balance-New
Set C = WSWUBalance.UsedRange.Find(WUDate, LookIn:=xlValues, lookat:=xlWhole)
If Not C Is Nothing Then
'WUDate already there
WSWUBalance.Cells(C.Row, "C") = WSWUBalance.Cells(C.Row, "C") + TColG
WSWUBalance.Cells(C.Row, "D") = WSWUBalance.Cells(C.Row, "D") + TColH
Else
'WUDate not there
Set C = WS.UsedRange.Find(WUDate, LookIn:=xlValues, lookat:=xlWhole)
If Not C Is Nothing Then
WSWUBalance.Cells(MaxRowWUBalance, "A") = C.Offset(, -6)
Else
WSWUBalance.Cells(MaxRowWUBalance, "A") = DateValue(Now)
End If
WSWUBalance.Cells(MaxRowWUBalance, "B") = WUDate
WSWUBalance.Cells(MaxRowWUBalance, "C") = TColG
WSWUBalance.Cells(MaxRowWUBalance, "D") = TColH
WSWUBalance.Cells(MaxRowWUBalance, "G").Formula = "=D" & MaxRowWUBalance & "*0.5%"
WSWUBalance.Cells(MaxRowWUBalance, "I").Formula = "=E" & MaxRowWUBalance & "+G" & MaxRowWUBalance & "-D" & MaxRowWUBalance
'Calculate Cumulative Totals
If MaxRowWUBalance = 2 Then
WSWUBalance.Cells(MaxRowWUBalance, "H").Formula = "=G" & MaxRowWUBalance
WSWUBalance.Cells(MaxRowWUBalance, "J").Formula = "=I" & MaxRowWUBalance
Else
WSWUBalance.Cells(MaxRowWUBalance, "H").Formula = "=G" & MaxRowWUBalance & "+H" & MaxRowWUBalance - 1
WSWUBalance.Cells(MaxRowWUBalance, "J").Formula = "=I" & MaxRowWUBalance & "+J" & MaxRowWUBalance - 1
End If
End If
'Re-Calculate new maxrow and new WUDate
MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
WUDate = WSWU.Cells(I, "P")
TColG = 0
TColH = 0
End If
End If
Next I
MsgBox ("Balance Amounts Updated successfully.")
'Close and Exit
WB.Close savechanges:=False
Set WB = Nothing
Set WSWU = Nothing
Set WS = Nothing
End If
End Sub4) Save the workbook
5) Attach the sheet you have send me and rename it to "WU-Balance-New" so you can keep the other WU Balance sheet there.
6) save and exit the workbook
to try it you need to activate the Import WU Confirmed Amounts and it will update automatically WU-Balance-New. I suggest for trial keep only the header in row1 and delete all the rest of the data and import again the last file received from Joseph and see the results
let me know
gowflow
In my precious post delete Sub ImportWUConfirmedAmts not Sub ImportWUConfirmedAmounts as this Sub doesn't exist.
gowflow
gowflow
ASKER
"well my problem is how to find the sum of Col I I need to report in WU-Balance-New ?"
Yes. We need Col i's totals for reporting and processing the batch.
"If I remember well Col I only sees a value when a batch is complete is this what you want the reporting of all the complete batches and not all the content of the file he send you for the col he fills (Col H Confirmed Amounts ???)"
Yes. Col i is the sum of Col H pickups.
Start with Col i. If it is the only value in WU-Staging-FBME that tells what Josef has in hand and, it is the only value in WU-Staging-FBME that we need to calculate the balance.
The Date? Not sure. Guess you can make that be the process is run? Like I said, this will probably need tweaking once I try it a few times.
You don't need G or H, just Col i.
Just take the value of Col i from WU-Staging-FBME, from there we subtract the wire he sends me and the commission he keeps. Col i - (wire + commission taken out) = balance. And, I want to see the history of each run of the code, not just a total that keeps updating.
Not sure how I can explain this any differently.
Sorry it's so hard to understand.
Yes. We need Col i's totals for reporting and processing the batch.
"If I remember well Col I only sees a value when a batch is complete is this what you want the reporting of all the complete batches and not all the content of the file he send you for the col he fills (Col H Confirmed Amounts ???)"
Yes. Col i is the sum of Col H pickups.
Start with Col i. If it is the only value in WU-Staging-FBME that tells what Josef has in hand and, it is the only value in WU-Staging-FBME that we need to calculate the balance.
The Date? Not sure. Guess you can make that be the process is run? Like I said, this will probably need tweaking once I try it a few times.
You don't need G or H, just Col i.
Just take the value of Col i from WU-Staging-FBME, from there we subtract the wire he sends me and the commission he keeps. Col i - (wire + commission taken out) = balance. And, I want to see the history of each run of the code, not just a total that keeps updating.
Not sure how I can explain this any differently.
Sorry it's so hard to understand.
ASKER
didn't see your latest before I responded to your questions. I'll look over the code and try it out. Thank you for your efforts.
ASKER
Thank you. I'll have to wait until Josef sends me his spreadsheet which won't be for a day or two as I need to first send him my pickup requests. In the mean time, if you wanted to work on the addition to the MCR / HMF function, I can post that question. Let me know. If you'd rather not at this time, that's fine.
No need if it is only the I your intrested in then for sure joseph file won't help coz it has every thing. It has all the records.
So let me recap then this is how will do it:
We create a button called Create WU Balance and it will work as follows: You will need to activate this Once to build up the WU-Balance-New from scratch with all the existing data in WU-Staging-FBME for all the values in Col I that are not blank.
Will also incorporate this same routine but handled diffrently for the daily updates.
Does it make sence ?
The only major diffrence now I see is that you want to pickup ONLY COMPLETED BATCHES !!!!!! and you never mentioned this anywhere in your post or your old question or anwhere EXCEPT in your last thread where I clearly asked you the question !!!! Sorry but this is a waste of time from your part you use workds like He picks up (which means nothing to me) where you could have used Complete batches (that I already worked on and can relate to) which would have made life much easier.
gowflow
So let me recap then this is how will do it:
We create a button called Create WU Balance and it will work as follows: You will need to activate this Once to build up the WU-Balance-New from scratch with all the existing data in WU-Staging-FBME for all the values in Col I that are not blank.
Will also incorporate this same routine but handled diffrently for the daily updates.
Does it make sence ?
The only major diffrence now I see is that you want to pickup ONLY COMPLETED BATCHES !!!!!! and you never mentioned this anywhere in your post or your old question or anwhere EXCEPT in your last thread where I clearly asked you the question !!!! Sorry but this is a waste of time from your part you use workds like He picks up (which means nothing to me) where you could have used Complete batches (that I already worked on and can relate to) which would have made life much easier.
gowflow
ASKER
Sorry for the confusion with this, gowflow, but I did state we needed to use Col i in my initial description:
"- Col B: 'Total Confirmed Received' - ongoing sum of Col i's (from WU-Staging-FBME) starting from 10/24/11 - a chosen starting date in Col B (from WU-Staging-FBME)"
"- Col D: 'Josef's Commission' - calculation: 5% of total of Col B (the total of Col i's - the confirmation of Josef's pickups)"
Sorry I didn't make that more clear.
So, where are we? I've put in your code as instructed. Do I need to do anything else?
"- Col B: 'Total Confirmed Received' - ongoing sum of Col i's (from WU-Staging-FBME) starting from 10/24/11 - a chosen starting date in Col B (from WU-Staging-FBME)"
"- Col D: 'Josef's Commission' - calculation: 5% of total of Col B (the total of Col i's - the confirmation of Josef's pickups)"
Sorry I didn't make that more clear.
So, where are we? I've put in your code as instructed. Do I need to do anything else?
As you liste din your last post col B and D please make an effort for the last time and list all columns starting from A with a description of each as with all these threads I am now starting to loose it. Need 1 place to focus on
gowflow
gowflow
Sorry but seems your confused not me !!!
Col A in the file you have send me is Date Pickup Request Sent not Date command is activated as menitoned in this question.
Col B in the file you have send me is Batch Number and not 'Total Confirmed Received'
Col C in the file you have send me is Amount Sent to Joseph
Col D in the file you have send me is Amount Confirmed Received not joseph's Commission
Col E in the file you have send me is Wire Sent to Sovereign
Col F in the file you have send me is Date Wire Received
Col G in the file you have send me is WU Commission
Col H in the file you have send me is Cumulative Commission
Col I in the file you have send me is Balance
Col J in the file you have send me is Cumulative Balance
So make up your mind on what you want and how you need it to be activated as i have been recveiving form you too many conflicting info so far.
gowflow
Col A in the file you have send me is Date Pickup Request Sent not Date command is activated as menitoned in this question.
Col B in the file you have send me is Batch Number and not 'Total Confirmed Received'
Col C in the file you have send me is Amount Sent to Joseph
Col D in the file you have send me is Amount Confirmed Received not joseph's Commission
Col E in the file you have send me is Wire Sent to Sovereign
Col F in the file you have send me is Date Wire Received
Col G in the file you have send me is WU Commission
Col H in the file you have send me is Cumulative Commission
Col I in the file you have send me is Balance
Col J in the file you have send me is Cumulative Balance
So make up your mind on what you want and how you need it to be activated as i have been recveiving form you too many conflicting info so far.
gowflow
ASKER
I started working on the below before I received your last post. Please look
this over and see if it meshes with what you are saying with some differences.
Start from WU-Staging-FBME where Col P = WUOct24-11
And we do the following:
1. Date: WU-Staging-FBME Col C to WUBalance Col A
2. Batch Number: WU-Staging-FBME Col P to Wu-Balance-New Col B
3. Amount Confirmed Received: WU-Staging-FBME Col i to Wu-Balance-New Col D
4. Wire Sent to Sovereign: From email (earlier attached message) the $ amount
contained to Wu-Balance-New Col E
5. Date Wire Received: Wu-Balance-New Col F - date above email received
6. WU Commission: Wu-Balance-New Col G: .5% of Wu-Balance-New Col D
7. Cumulative Commission: Wu-Balance-New Col H (cumulative amounts of Col G)
8. Balance: Wu-Balance-New Col i. Col i = Col D -(Col E + Col G)
9. Cumulative Balance: Not sure we need Col J (Cumulative Balance) of Wu-
Balance-New
We do not need Wu-Balance-New Col C. We do not need to know how much I told
Josef to pick up as indicated in WU-Staging-FBME Col G
this over and see if it meshes with what you are saying with some differences.
Start from WU-Staging-FBME where Col P = WUOct24-11
And we do the following:
1. Date: WU-Staging-FBME Col C to WUBalance Col A
2. Batch Number: WU-Staging-FBME Col P to Wu-Balance-New Col B
3. Amount Confirmed Received: WU-Staging-FBME Col i to Wu-Balance-New Col D
4. Wire Sent to Sovereign: From email (earlier attached message) the $ amount
contained to Wu-Balance-New Col E
5. Date Wire Received: Wu-Balance-New Col F - date above email received
6. WU Commission: Wu-Balance-New Col G: .5% of Wu-Balance-New Col D
7. Cumulative Commission: Wu-Balance-New Col H (cumulative amounts of Col G)
8. Balance: Wu-Balance-New Col i. Col i = Col D -(Col E + Col G)
9. Cumulative Balance: Not sure we need Col J (Cumulative Balance) of Wu-
Balance-New
We do not need Wu-Balance-New Col C. We do not need to know how much I told
Josef to pick up as indicated in WU-Staging-FBME Col G
Tks much clearer now. So to summarize in 2 words you need to GROUP per batch number (Col P in WU-Staging-FBME) all the col I where there is a value and the rest of columns to follow.
If above assumption is correct then I have 2 major questions:
QUESTION 1)
===========
Say WUOct25-11 (as an example just a date) and in this date there are say 10 batches and say Joseph sends you a file that has 3 batches of these completed (lets say $3500) and say now we are creating WU-Balance and say its the only record there then we should have
Oct 25,11 WUOct25-11 3500 3500*5% etc ...
Then 2 days later he sends 2 more batches toalling $2500 in this same WUOct25-11
how do you want to see the new WU-Balance-New ?
Oct 25,11 WUOct25-11 3500 3500*5% etc ...
Oct 25,11 WUOct25-11 2500 2500*5% etc ...
OR
Oct 25,11 WUOct25-11 6000 6000*5% etc ...
QUESTION 2)
===========
When we read the email where do we put the value of the email ??? Say in the file we have 6 lines and the email value is 43000 and the date is Nov 2,11 and say there is no date Nov 2, 11 in the file
Oct 24,11 WUOct24-11 2000 2000*5% etc ...
Oct 25,11 WUOct25-11 6000 6000*5% etc ...
Oct 26,11 WUOct26-11 2500 2500*5% etc ...
Oct 27,11 WUOct27-11 4000 4000*5% etc ...
Nov1,11 WUNov1-11 7600 7600*5% etc ...
Nov5,11 WUNov5-11 8300 8300*5% etc....
WHERE to put the 43000 of Nov 2, 2011 ???
gowflow
If above assumption is correct then I have 2 major questions:
QUESTION 1)
===========
Say WUOct25-11 (as an example just a date) and in this date there are say 10 batches and say Joseph sends you a file that has 3 batches of these completed (lets say $3500) and say now we are creating WU-Balance and say its the only record there then we should have
Oct 25,11 WUOct25-11 3500 3500*5% etc ...
Then 2 days later he sends 2 more batches toalling $2500 in this same WUOct25-11
how do you want to see the new WU-Balance-New ?
Oct 25,11 WUOct25-11 3500 3500*5% etc ...
Oct 25,11 WUOct25-11 2500 2500*5% etc ...
OR
Oct 25,11 WUOct25-11 6000 6000*5% etc ...
QUESTION 2)
===========
When we read the email where do we put the value of the email ??? Say in the file we have 6 lines and the email value is 43000 and the date is Nov 2,11 and say there is no date Nov 2, 11 in the file
Oct 24,11 WUOct24-11 2000 2000*5% etc ...
Oct 25,11 WUOct25-11 6000 6000*5% etc ...
Oct 26,11 WUOct26-11 2500 2500*5% etc ...
Oct 27,11 WUOct27-11 4000 4000*5% etc ...
Nov1,11 WUNov1-11 7600 7600*5% etc ...
Nov5,11 WUNov5-11 8300 8300*5% etc....
WHERE to put the 43000 of Nov 2, 2011 ???
gowflow
ok here it is I think this is what you want. This version works as follows:
Everytime you activate the 'Update WU Balance' button in sheet Main the system will prompt you for a Start Date (which is the beginning of the Balance to keep track we can refine this later when the whole process works well) after the date is input it will look in 'WU-Staging-FBME' for all records where there is an amount in Col I and that is after the mention date in Col C and will fill a new sheet called "WU-Balance-New" that is attached here. Any time this button is activated if there are new items it will record them at the end of the existing ones and if it encounter items that were already there it will override the existing items with the info it just read from the file (this will allow you in case you have corrections made in WU-Staging-FBME for amounts or dates to be automatically corrected in WU-Balance-New. This version does not incorporate the reading of email as at time of publishing the solution you haven't answered to my previous querrry. We can still add this part lateron.
To implement.
1) Make a copy of the latest Visa you have even if it is the one including the solution I posted earlier to which I think is not suitable for you. The code I posted here will replace the previous version correctly.
2) Doubleclick on module1 and select to view 1 sub at a time by clicking on the bottom left icon.
3) Select Sub ImportWUConfirmedAmts and delete it.
4) Copy the below code (SELECT ALL right click COPY and paste anfter any End Sub.
5) SAVE the workbook.
6) If you already have 'WU-Balance-New' sheet in your visa file delete it and replace it by the one attached here.
WU-Balance-New.xlsx
7) SAVE your workbook.
8) Check out the below image and create a button on Main and call it Update WU Balance and change its color like in the below picture.
9) If you forgot how to create a button here it is: Select Developpment from the main menu and make sure Design is clicked. Select sheet Main right click on button 'Import WU Confirmed Amounts' select copy and paste it beside it. right click on the new button created choose property and change its caption to update WU Balance and change its background color to purpule or any color that suits you. Cilck anywhere else on the sheet to remove the focus.
10) SAVE the workbook.
11) doubleclick on Update WU Balance and paste the below code between Private Sub commandbutton_click and End Sub
12) SAVE the workbook and Exit.
13) Open the workbook and simply try it by doing this:
Activate the button Update WU Balance, choose a date (you can choose say July 1, 2011 just to see how it will display once it is finished you can close the workbook without saving and open it again and try an other date etc ... or even if you save the workbook after choosing a date you can still DELETE the whole rows that were inputed in WU-Balance-New (Except the Green Header and try with a new date) Try also running it for a date then running it again for the same date and see if anything changes. Try also after runing it for a certain date goback to WU-Staging-FBME and take a batch that is not complete and completed manually and run WU balance and see what happens.
You do not need joseph to test your file you can simulate and type data manually if this needed.
At the end when you decide on a date (you mentioned Oct 24, 2011) each time you run Update balance will default it to be that date so you don;t need to enter it every time.
Enjoy and pls let me know oyur comments.
gowflow
Everytime you activate the 'Update WU Balance' button in sheet Main the system will prompt you for a Start Date (which is the beginning of the Balance to keep track we can refine this later when the whole process works well) after the date is input it will look in 'WU-Staging-FBME' for all records where there is an amount in Col I and that is after the mention date in Col C and will fill a new sheet called "WU-Balance-New" that is attached here. Any time this button is activated if there are new items it will record them at the end of the existing ones and if it encounter items that were already there it will override the existing items with the info it just read from the file (this will allow you in case you have corrections made in WU-Staging-FBME for amounts or dates to be automatically corrected in WU-Balance-New. This version does not incorporate the reading of email as at time of publishing the solution you haven't answered to my previous querrry. We can still add this part lateron.
To implement.
1) Make a copy of the latest Visa you have even if it is the one including the solution I posted earlier to which I think is not suitable for you. The code I posted here will replace the previous version correctly.
2) Doubleclick on module1 and select to view 1 sub at a time by clicking on the bottom left icon.
3) Select Sub ImportWUConfirmedAmts and delete it.
4) Copy the below code (SELECT ALL right click COPY and paste anfter any End Sub.
Sub UpdateWUBalance(ByVal StartDate As Date)
Dim WS As Worksheet
Dim WSWUBalance As Worksheet
Dim I As Long, J As Long
Dim MaxRow As Long, MaxRowWUBalance As Long
Dim Rng As Range
Dim TConfirmed As Double, TColI As Double, TColH As Double
Dim WUFile As String, WUDate As String
Dim C As Range
'Update WU-Balance-New
Set WS = Sheets("WU-Staging-FBME")
Set WSWUBalance = Sheets("WU-Balance-New")
MaxRow = WS.Rows(WS.Rows.Count).End(xlUp).Row
MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
'>> 1 >> Filter WU-Staging-FBME for dates > Oct 24, 2011
WS.UsedRange.AutoFilter Field:=10, Criteria1:=">=" & DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
WS.UsedRange.AutoFilter Field:=9, Criteria1:=">=" & 0
TColI = 0
WUDate = ""
WUFile = ""
'>> 2 >> Add Col I
For I = 2 To MaxRow
If WS.Range(I & ":" & I).EntireRow.Hidden = False Then
If WUDate = "" Then
WUDate = WS.Cells(I, "C")
WUFile = WS.Cells(I, "P")
End If
If WS.Cells(I, "P") <> "" Then
If WS.Cells(I, "C") = WUDate Then
TColI = TColI + WS.Cells(I, "I")
Else
'Lookfor WUDate in sheet 'WU-Balance-New
'Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True)
Set C = WSWUBalance.UsedRange.Find(What:=WUDate, LookIn:=xlFormulas, lookat:=xlWhole)
If Not C Is Nothing Then
'WUDate already there
WSWUBalance.Cells(C.Row, "A") = WUDate
WSWUBalance.Cells(C.Row, "B") = WUFile
WSWUBalance.Cells(C.Row, "D") = TColI
Else
'WUDate not there
WSWUBalance.Cells(MaxRowWUBalance, "A") = WUDate
WSWUBalance.Cells(MaxRowWUBalance, "B") = WUFile
'WSWUBalance.Cells(MaxRowWUBalance, "C") = TColG
WSWUBalance.Cells(MaxRowWUBalance, "D") = TColI
WSWUBalance.Cells(MaxRowWUBalance, "G").Formula = "=D" & MaxRowWUBalance & "*0.5%"
WSWUBalance.Cells(MaxRowWUBalance, "I").Formula = "=E" & MaxRowWUBalance & "+G" & MaxRowWUBalance & "-D" & MaxRowWUBalance
'Calculate Cumulative Totals
If MaxRowWUBalance = 2 Then
WSWUBalance.Cells(MaxRowWUBalance, "H").Formula = "=G" & MaxRowWUBalance
WSWUBalance.Cells(MaxRowWUBalance, "J").Formula = "=I" & MaxRowWUBalance
Else
WSWUBalance.Cells(MaxRowWUBalance, "H").Formula = "=G" & MaxRowWUBalance & "+H" & MaxRowWUBalance - 1
WSWUBalance.Cells(MaxRowWUBalance, "J").Formula = "=I" & MaxRowWUBalance & "+J" & MaxRowWUBalance - 1
End If
End If
'Re-Calculate new maxrow and new WUDate
MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
WUDate = WS.Cells(I, "C")
WUFile = WS.Cells(I, "P")
TColI = WS.Cells(I, "I")
End If
End If
End If
Next I
WS.ShowAllData
WS.AutoFilterMode = False
MsgBox ("Balance Amounts Updated successfully.")
End Sub
Sub ImportWUConfirmedAmts()
Dim WS As Worksheet
Dim WSWU As Worksheet
Dim WB As Workbook
Dim I As Long, J As Long
Dim MaxRowWU As Long, MaxRowWUBalance As Long
Dim Rng As Range
Dim TConfirmed As Double, TColG As Double, TColH As Double
Dim WUFile As String, WUDate As String
Dim C
If MsgBox("Are you ready to Import WU Confirmed Amounts ?", vbQuestion + vbYesNo, "Import WU Confirmed Amounts") = vbYes Then
Do
WUFile = GFileName(gstFolderWesternUnion)
If WUFile = "" Then
If MsgBox("No file has been selected" & Chr(10) _
& "[OK] to continue and select a file." & Chr(10) _
& "[Cancel] to Exit." & Chr(10) & Chr(10) _
& "Please make a selection.", vbInformation + vbOKCancel, "Import WU Confirmed Amounts") = vbCancel Then
Exit Sub
End If
End If
Loop Until WUFile <> ""
Set WS = Sheets("WU-Staging-FBME")
'Set WSWUBalance = Sheets("WU-Balance-New")
Set WB = Workbooks.Open(WUFile)
Set WSWU = ActiveSheet
MaxRowWU = WSWU.Rows(WSWU.Rows.Count).End(xlUp).Row
'MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
For I = 3 To MaxRowWU
If WSWU.Cells(I, "H") <> "" Then
WS.UsedRange.AutoFilter 4, WSWU.Cells(I, "D")
WS.UsedRange.AutoFilter 3, Criteria1:=">=" & WSWU.Cells(I, "C"), Operator:=xlAnd, Criteria2:="<=" & WSWU.Cells(I, "C")
WS.UsedRange.AutoFilter 2, WSWU.Cells(I, "B")
Set Rng = WS.UsedRange.SpecialCells(xlCellTypeVisible)
For Each Row In Rng.EntireRow
If Not Row.Row = 1 Then
If WSWU.Cells(I, "A") = WS.Cells(Row.Row, "A") And WSWU.Cells(I, "E") = WS.Cells(Row.Row, "E") And WSWU.Cells(I, "F") = WS.Cells(Row.Row, "F") And WS.Cells(Row.Row, "H").Value = "" Then
WS.Cells(Row.Row, "H").Value = WSWU.Cells(I, "H").Value
TConfirmed = TConfirmed + WSWU.Cells(I, "H").Value
J = J + 1
Exit For
End If
End If
Next Row
End If
Next I
WS.ShowAllData
WS.AutoFilterMode = False
MsgBox ("Confirmed Amounts Updated successfully for " & J & " records totalling " & TConfirmed)
'Close and Exit
WB.Close savechanges:=False
Set WB = Nothing
Set WSWU = Nothing
Set WS = Nothing
End If
End Sub5) SAVE the workbook.
6) If you already have 'WU-Balance-New' sheet in your visa file delete it and replace it by the one attached here.
WU-Balance-New.xlsx
7) SAVE your workbook.
8) Check out the below image and create a button on Main and call it Update WU Balance and change its color like in the below picture.
9) If you forgot how to create a button here it is: Select Developpment from the main menu and make sure Design is clicked. Select sheet Main right click on button 'Import WU Confirmed Amounts' select copy and paste it beside it. right click on the new button created choose property and change its caption to update WU Balance and change its background color to purpule or any color that suits you. Cilck anywhere else on the sheet to remove the focus.
10) SAVE the workbook.
11) doubleclick on Update WU Balance and paste the below code between Private Sub commandbutton_click and End Sub
Dim SDate As String
Do
SDate = InputBox("Please Enter Start Date for beginning tracking WU Balance", "Start Date", "10/18/2011")
Loop Until IsDate(SDate)
UpdateWUBalance SDate12) SAVE the workbook and Exit.
13) Open the workbook and simply try it by doing this:
Activate the button Update WU Balance, choose a date (you can choose say July 1, 2011 just to see how it will display once it is finished you can close the workbook without saving and open it again and try an other date etc ... or even if you save the workbook after choosing a date you can still DELETE the whole rows that were inputed in WU-Balance-New (Except the Green Header and try with a new date) Try also running it for a date then running it again for the same date and see if anything changes. Try also after runing it for a certain date goback to WU-Staging-FBME and take a batch that is not complete and completed manually and run WU balance and see what happens.
You do not need joseph to test your file you can simulate and type data manually if this needed.
At the end when you decide on a date (you mentioned Oct 24, 2011) each time you run Update balance will default it to be that date so you don;t need to enter it every time.
Enjoy and pls let me know oyur comments.
gowflow
ASKER
Wow! That is a lot to go over. I'll take a look at it later today. Had some errands to run earlier and now. I'll let you know. You're quite a guy, gowflow. (That is a compliment.)
Later....
Later....
ASKER
Josef and I are kind of on a hold pattern until I'm able to send him more pickup requests. I can't send him any more (per my boss) until he bring his balance with us back to zero, so I'm awaiting those wires, so can't test out this code yet, sorry.
In the mean time, I have another project you might have fun with: I need to track - via a graph chart in excel - our monthly totals for different criteria. If you're interested in doing that before the closure of this question, let me know and I'll post the question here.
In the mean time, I have another project you might have fun with: I need to track - via a graph chart in excel - our monthly totals for different criteria. If you're interested in doing that before the closure of this question, let me know and I'll post the question here.
ok go ahead and post it but first is it related to visa/mastercard files or it has nothignto do with it ?
gowflow
gowflow
ASKER
Great! This graph project uses the Visa workbook and the Mastercard workbook you have.
here it is:
https://www.experts-exchange.com/questions/27433750/graph-the-results-of-multiple-spreadsheets.html
here it is:
https://www.experts-exchange.com/questions/27433750/graph-the-results-of-multiple-spreadsheets.html
Why can't u test the file like I proposed ??
gowflow
gowflow
ASKER
I'll get back to this one today
ASKER
Problems with the WU Balance:
- Start Date dialog box opens, but clicking Cancel will not close it.
- I need Col D (Amount Confirmed Received) to tally the amounts in Col H, not Col i because, Col i does not show all the confirmed amounts received. I know this is a change from what I originally stated.
- go ahead and input the tally of Amount Sent to Joseph into Col C tallied from Col G of WU-Staging-FBME
Let's start off with this and see where it gets us. For some reason I'm not getting the right results.
- Start Date dialog box opens, but clicking Cancel will not close it.
- I need Col D (Amount Confirmed Received) to tally the amounts in Col H, not Col i because, Col i does not show all the confirmed amounts received. I know this is a change from what I originally stated.
- go ahead and input the tally of Amount Sent to Joseph into Col C tallied from Col G of WU-Staging-FBME
Let's start off with this and see where it gets us. For some reason I'm not getting the right results.
Sorry but do not understand what you want : What do you mean by tally ? I read your post 7 times and still don't know what to do.
I hv modified the routine to allow cancel
Load the file and press on design in Developper menu and double click in sheet main on the button 'Update WU Balance' and replace the code inside the Sub by the following. Delete everything that is between Sub and end Sub and paste the below code. Save Exit and explain clearly what you want me to change.
gowflow
I hv modified the routine to allow cancel
Load the file and press on design in Developper menu and double click in sheet main on the button 'Update WU Balance' and replace the code inside the Sub by the following. Delete everything that is between Sub and end Sub and paste the below code. Save Exit and explain clearly what you want me to change.
gowflow
Dim SDate As String
Do
SDate = InputBox("Please Enter Start Date for beginning tracking WU Balance", "Start Date", "10/18/2011")
Loop Until IsDate(SDate) Or SDate = ""
If SDate <> "" Then
UpdateWUBalance SDate
End IfASKER
What I mean by tally is 'sum', the total.
Prior Request:
1. Amount Confirmed Received: WU-Staging-FBME Col i to Wu-Balance-New Col D
Change to:
Sum of WU-Staging-FBME Col H to Wu-Balance-New Col D WHERE the value in Col M (Cardholder) is the same value (such as Jen110511) AND the date I indicate in the dialog box is equal to and greater than the date in Col C (Date WU Notice Recv'd)
For example, if in the dialog box I input the date 10/28/11 it would begin by filtering for all dates from 10/28/11 in Col C (Date WU Notice Recv'd) and after, then find all entries in WU-Staging-FBME Col H that had a value of Jen110511 in Col M of WU-Staging-FBME, sum those values and put that total in Wu-Balance-New Col D
Prior request:
2. We do not need Wu-Balance-New Col C. We do not need to know how much I told
Change to:
Input the total of Amount Sent to Joseph Col G from WU-Staging-FBME - WHERE the value in Col M (Cardholder) is the same value (such as Jen110511) - to Wu-Balance-New Col C AND the date I indicated in the dialog box is equal to and greater than the date in Col C (Date WU Notice Recv'd)
Is that any clearer?
Prior Request:
1. Amount Confirmed Received: WU-Staging-FBME Col i to Wu-Balance-New Col D
Change to:
Sum of WU-Staging-FBME Col H to Wu-Balance-New Col D WHERE the value in Col M (Cardholder) is the same value (such as Jen110511) AND the date I indicate in the dialog box is equal to and greater than the date in Col C (Date WU Notice Recv'd)
For example, if in the dialog box I input the date 10/28/11 it would begin by filtering for all dates from 10/28/11 in Col C (Date WU Notice Recv'd) and after, then find all entries in WU-Staging-FBME Col H that had a value of Jen110511 in Col M of WU-Staging-FBME, sum those values and put that total in Wu-Balance-New Col D
Prior request:
2. We do not need Wu-Balance-New Col C. We do not need to know how much I told
Change to:
Input the total of Amount Sent to Joseph Col G from WU-Staging-FBME - WHERE the value in Col M (Cardholder) is the same value (such as Jen110511) - to Wu-Balance-New Col C AND the date I indicated in the dialog box is equal to and greater than the date in Col C (Date WU Notice Recv'd)
Is that any clearer?
AAAAAhhhhhaaaaa !!!!! Your coming to your senses !!!! That's eactly what I was proposing when you first started to think of the idea of this WU Balance I told you then you need to compare amounts send to him with what he confirmed !!! ... Well seems you got to it now. Good
Check out this version. To implement do the following:
1) Make a copy of your latest Visa file and give it a new name
2) Open the new workbook and click on sheet 'WU-Balance-New'
3) We need to add a column pls click on Col E like in the below image and right click choose Insert
4) Click on Cell E1 and Name the Col 'Difference'
5) Select the whole Col E and right click and choose Format Cell like in the below image and choose Number 2 Decimal places and the red format with tick in the coma like the below picture.
6) SAVE the workbook
7) Goto VBA and doubleclik on Module1 click on the bottom left icon to view 1 sub at a time select the following Sub and delete it
UpdateWUBalance
8) SELECT ALL in the below code choose right click COPY and paste after any End Sub in Module1
9) Save the workbook Exit and load it again and enable macros.
10) If you have anything in the Sheet WU-Balance-New select from row 2 to whatever row there is data and delete them.
11) Activate the button Update WU Balance and check the results. the col E was created for oyur easy reference to compare values sent to values confirmed. If they are the same nothing is there if he confirmed more the amount is positive and if less the amount is negative. I guess this way you can spot directly diffrences.
Let me know
gowflow
Check out this version. To implement do the following:
1) Make a copy of your latest Visa file and give it a new name
2) Open the new workbook and click on sheet 'WU-Balance-New'
3) We need to add a column pls click on Col E like in the below image and right click choose Insert
4) Click on Cell E1 and Name the Col 'Difference'
5) Select the whole Col E and right click and choose Format Cell like in the below image and choose Number 2 Decimal places and the red format with tick in the coma like the below picture.
6) SAVE the workbook
7) Goto VBA and doubleclik on Module1 click on the bottom left icon to view 1 sub at a time select the following Sub and delete it
UpdateWUBalance
8) SELECT ALL in the below code choose right click COPY and paste after any End Sub in Module1
Sub UpdateWUBalance(ByVal StartDate As Date)
Dim WS As Worksheet
Dim WSWUBalance As Worksheet
Dim I As Long, J As Long
Dim MaxRow As Long, MaxRowWUBalance As Long
Dim Rng As Range
Dim TConfirmed As Double, TColI As Double, TColH As Double, TColG As Double
Dim WUFile As String, WUDate As String
Dim C As Range
'Update WU-Balance-New
Set WS = Sheets("WU-Staging-FBME")
Set WSWUBalance = Sheets("WU-Balance-New")
MaxRow = WS.Rows(WS.Rows.Count).End(xlUp).Row
MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
'>> 1 >> Filter WU-Staging-FBME for dates > Oct 24, 2011
'Old way of Filtering Prior to 25/11/2011
'WS.UsedRange.AutoFilter Field:=10, Criteria1:=">=" & DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
'WS.UsedRange.AutoFilter Field:=9, Criteria1:=">=" & 0
'New way of Filtering After 25/11/2011
WS.UsedRange.AutoFilter Field:=3, Criteria1:=">=" & DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
WS.UsedRange.AutoFilter Field:=8, Criteria1:=">=" & 0
TColG = 0
TColH = 0
TColI = 0
WUDate = ""
WUFile = ""
'>> 2 >> Add Col I
For I = 2 To MaxRow
If WS.Range(I & ":" & I).EntireRow.Hidden = False Then
If WUDate = "" Then
WUDate = WS.Cells(I, "C")
'Old Way prior to 25/11/2011
'WUFile = WS.Cells(I, "P")
'New Way After 25/11/2011
WUFile = WS.Cells(I, "M")
End If
'Old Way prior to 25/11/2011
'If WS.Cells(I, "P") <> "" Then
'New Way After 25/11/2011
If WS.Cells(I, "M") <> "" Then
If WS.Cells(I, "C") = WUDate Then
'Old Way prior to 25/11/2011
'TColI = TColI + WS.Cells(I, "I")
'New Way After 25/11/2011
TColH = TColH + Val(WS.Cells(I, "H"))
TColG = TColG + Val(WS.Cells(I, "G"))
Else
'Lookfor WUDate in sheet 'WU-Balance-New
'Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True)
Set C = WSWUBalance.UsedRange.Find(What:=WUDate, LookIn:=xlFormulas, lookat:=xlWhole)
If Not C Is Nothing Then
'WUDate already there
WSWUBalance.Cells(C.Row, "A") = WUDate
WSWUBalance.Cells(C.Row, "B") = WUFile
WSWUBalance.Cells(C.Row, "C") = TColG
WSWUBalance.Cells(C.Row, "D") = TColH
WSWUBalance.Cells(C.Row, "E").Formula = "=IF(D" & C.Row & "-C" & C.Row & "<>0,D" & C.Row & "-C" & C.Row & ","""")"
Else
'WUDate not there
WSWUBalance.Cells(MaxRowWUBalance, "A") = WUDate
WSWUBalance.Cells(MaxRowWUBalance, "B") = WUFile
WSWUBalance.Cells(MaxRowWUBalance, "C") = TColG
WSWUBalance.Cells(MaxRowWUBalance, "D") = TColH
WSWUBalance.Cells(MaxRowWUBalance, "E").Formula = "=IF(D" & MaxRowWUBalance & "-C" & MaxRowWUBalance & "<>0,D" & MaxRowWUBalance & "-C" & MaxRowWUBalance & ","""")"
WSWUBalance.Cells(MaxRowWUBalance, "H").Formula = "=D" & MaxRowWUBalance & "*0.5%"
WSWUBalance.Cells(MaxRowWUBalance, "J").Formula = "=F" & MaxRowWUBalance & "+H" & MaxRowWUBalance & "-D" & MaxRowWUBalance
'Calculate Cumulative Totals
If MaxRowWUBalance = 2 Then
WSWUBalance.Cells(MaxRowWUBalance, "I").Formula = "=H" & MaxRowWUBalance
WSWUBalance.Cells(MaxRowWUBalance, "K").Formula = "=J" & MaxRowWUBalance
Else
WSWUBalance.Cells(MaxRowWUBalance, "I").Formula = "=H" & MaxRowWUBalance & "+I" & MaxRowWUBalance - 1
WSWUBalance.Cells(MaxRowWUBalance, "K").Formula = "=J" & MaxRowWUBalance & "+K" & MaxRowWUBalance - 1
End If
End If
'Re-Calculate new maxrow and new WUDate
MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
'Old Way prior to 25/11/2011
'WUDate = WS.Cells(I, "C")
'WUFile = WS.Cells(I, "P")
'TColI = WS.Cells(I, "I")
'New Way After 25/11/2011
WUDate = WS.Cells(I, "C")
WUFile = WS.Cells(I, "M")
TColH = WS.Cells(I, "H")
TColG = WS.Cells(I, "G")
End If
End If
End If
Next I
WS.ShowAllData
WS.AutoFilterMode = False
WSWUBalance.Range("B:B").HorizontalAlignment = xlLeft
WSWUBalance.Columns(2).AutoFit
MsgBox ("Balance Amounts Updated successfully.")
End Sub9) Save the workbook Exit and load it again and enable macros.
10) If you have anything in the Sheet WU-Balance-New select from row 2 to whatever row there is data and delete them.
11) Activate the button Update WU Balance and check the results. the col E was created for oyur easy reference to compare values sent to values confirmed. If they are the same nothing is there if he confirmed more the amount is positive and if less the amount is negative. I guess this way you can spot directly diffrences.
Let me know
gowflow
ASKER
Some errors in calculation - see attached files.
In WU_Balance_Summary.gif it shows incorrectly that Adam102711 had a confirmed amount of 11,410.00 when that is actually the total of Adam 102711 and Jen102711.
Also, WU Balance Summary.gif show that Shawn110411 had a total of 5,980 when that is actually the total of Shawn110411 and Adam10411.
In addition, I had a number of amounts strung down the sheet in Col H that totaled $17,050.00. They had different values in Col M. Two things wrong: it assigned the total to one value: Jen110511
and gave the wrong amount of 3,350.00 in both Col C and Col D.
In WU_Balance_Summary.gif it shows incorrectly that Adam102711 had a confirmed amount of 11,410.00 when that is actually the total of Adam 102711 and Jen102711.
Also, WU Balance Summary.gif show that Shawn110411 had a total of 5,980 when that is actually the total of Shawn110411 and Adam10411.
In addition, I had a number of amounts strung down the sheet in Col H that totaled $17,050.00. They had different values in Col M. Two things wrong: it assigned the total to one value: Jen110511
and gave the wrong amount of 3,350.00 in both Col C and Col D.
Ur right for the calculation ... Big mess ... my fault.
question
you want them grouped by Col M ONLY or Col M and also by Date in C ?
like if we have
Col M Col C
Jen 10/18/2011
Jen 10/18/2011
Jen 10/20/2011
Jen 10/20/2011
Jen 10/20/2011
I group and sum all the Jen in this case or group the first 2 under 10/18/2011 and make a line hten group the next 3 under 10/20/2011 and make a line ???
gowflow
question
you want them grouped by Col M ONLY or Col M and also by Date in C ?
like if we have
Col M Col C
Jen 10/18/2011
Jen 10/18/2011
Jen 10/20/2011
Jen 10/20/2011
Jen 10/20/2011
I group and sum all the Jen in this case or group the first 2 under 10/18/2011 and make a line hten group the next 3 under 10/20/2011 and make a line ???
gowflow
ASKER
Col M and also by Date in C
Yes, in grouping: group the first 2 under 10/18/2011 and make a line then group the next 3 under 10/20/2011 and make a line.
Yes, in grouping: group the first 2 under 10/18/2011 and make a line then group the next 3 under 10/20/2011 and make a line.
ok here it is
1) make a copy of latest visa file and give it a new name.
2) goto vba and doubleclick on module1 and select bottom left icon to view 1 sub at a time
3) choose sub UpdateWUBalance and delete it.
4) SELECT ALL from the below code and right click choose COPY and paste after any end sub
5) SAVE and EXIT
6) open it and give it a try.
Pls let me know
gowflow
1) make a copy of latest visa file and give it a new name.
2) goto vba and doubleclick on module1 and select bottom left icon to view 1 sub at a time
3) choose sub UpdateWUBalance and delete it.
4) SELECT ALL from the below code and right click choose COPY and paste after any end sub
Sub UpdateWUBalance(ByVal StartDate As Date)
Dim WS As Worksheet
Dim WSWUBalance As Worksheet
Dim I As Long, J As Long
Dim MaxRow As Long, MaxRowWUBalance As Long
Dim Rng As Range
Dim TConfirmed As Double, TColI As Double, TColH As Double, TColG As Double
Dim WUFile As String, WUDate As String
Dim C As Range
Dim Tmp
Dim FirstAddress As String
Dim FoundIt As Boolean
'Update WU-Balance-New
Set WS = Sheets("WU-Staging-FBME")
Set WSWUBalance = Sheets("WU-Balance-New")
MaxRow = WS.Rows(WS.Rows.Count).End(xlUp).Row
MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
'>> 1 >> Filter WU-Staging-FBME for dates > Oct 24, 2011
'Old way of Filtering Prior to 25/11/2011
'WS.UsedRange.AutoFilter Field:=10, Criteria1:=">=" & DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
'WS.UsedRange.AutoFilter Field:=9, Criteria1:=">=" & 0
'New way of Filtering After 25/11/2011
WS.UsedRange.AutoFilter Field:=3, Criteria1:=">=" & DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
WS.UsedRange.AutoFilter Field:=8, Criteria1:=">=" & 0
TColG = 0
TColH = 0
TColI = 0
WUDate = ""
WUFile = ""
'>> 2 >> Add Col I
For I = 2 To MaxRow
If WS.Range(I & ":" & I).EntireRow.Hidden = False Then
If WUDate = "" Then
WUDate = WS.Cells(I, "C")
'Old Way prior to 25/11/2011
'WUFile = WS.Cells(I, "P")
'New Way After 25/11/2011
WUFile = WS.Cells(I, "M")
End If
'Old Way prior to 25/11/2011
'If WS.Cells(I, "P") <> "" Then
'New Way After 25/11/2011
'If WS.Cells(I, "M") <> "" Then
If WS.Cells(I, "M") = WUFile And WS.Cells(I, "C") = WUDate Then
'Old Way prior to 25/11/2011
'TColI = TColI + WS.Cells(I, "I")
'New Way After 25/11/2011
TColH = TColH + GetValue(WS.Cells(I, "H"))
TColG = TColG + GetValue(WS.Cells(I, "G"))
Else
'Lookfor WUDate in sheet 'WU-Balance-New
'Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True)
FoundIt = False
With WSWUBalance.UsedRange
Set C = .Find(What:=WUDate, LookIn:=xlFormulas, lookat:=xlWhole)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If WSWUBalance.Range("B" & C.Row) = WUFile Then
FoundIt = True
Exit Do
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If
End With
If FoundIt Then
'WUDate already there
WSWUBalance.Cells(C.Row, "A") = WUDate
WSWUBalance.Cells(C.Row, "B") = WUFile
WSWUBalance.Cells(C.Row, "C") = TColG
WSWUBalance.Cells(C.Row, "D") = TColH
WSWUBalance.Cells(C.Row, "E").Formula = "=IF(D" & C.Row & "-C" & C.Row & "<>0,D" & C.Row & "-C" & C.Row & ","""")"
Else
'WUDate not there
WSWUBalance.Cells(MaxRowWUBalance, "A") = WUDate
WSWUBalance.Cells(MaxRowWUBalance, "B") = WUFile
WSWUBalance.Cells(MaxRowWUBalance, "C") = TColG
WSWUBalance.Cells(MaxRowWUBalance, "D") = TColH
WSWUBalance.Cells(MaxRowWUBalance, "E").Formula = "=IF(D" & MaxRowWUBalance & "-C" & MaxRowWUBalance & "<>0,D" & MaxRowWUBalance & "-C" & MaxRowWUBalance & ","""")"
WSWUBalance.Cells(MaxRowWUBalance, "H").Formula = "=D" & MaxRowWUBalance & "*0.5%"
WSWUBalance.Cells(MaxRowWUBalance, "J").Formula = "=F" & MaxRowWUBalance & "+H" & MaxRowWUBalance & "-D" & MaxRowWUBalance
'Calculate Cumulative Totals
If MaxRowWUBalance = 2 Then
WSWUBalance.Cells(MaxRowWUBalance, "I").Formula = "=H" & MaxRowWUBalance
WSWUBalance.Cells(MaxRowWUBalance, "K").Formula = "=J" & MaxRowWUBalance
Else
WSWUBalance.Cells(MaxRowWUBalance, "I").Formula = "=H" & MaxRowWUBalance & "+I" & MaxRowWUBalance - 1
WSWUBalance.Cells(MaxRowWUBalance, "K").Formula = "=J" & MaxRowWUBalance & "+K" & MaxRowWUBalance - 1
End If
End If
'Re-Calculate new maxrow and new WUDate
MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
'Old Way prior to 25/11/2011
'WUDate = WS.Cells(I, "C")
'WUFile = WS.Cells(I, "P")
'TColI = WS.Cells(I, "I")
'New Way After 25/11/2011
WUDate = WS.Cells(I, "C")
WUFile = WS.Cells(I, "M")
TColH = GetValue(WS.Cells(I, "H"))
TColG = GetValue(WS.Cells(I, "G"))
End If
'End If
End If
Next I
WS.ShowAllData
WS.AutoFilterMode = False
WSWUBalance.Range("B:B").HorizontalAlignment = xlLeft
WSWUBalance.Columns(2).AutoFit
MsgBox ("Balance Amounts Updated successfully.")
End Sub
Function GetValue(Item As Variant) As Double
Dim Tmp As Variant
Dim J As Long
GetValue = 0
If IsNumeric(Item) Then
GetValue = Item
Else
Tmp = Split(Item, " ")
For J = 0 To UBound(Tmp)
If IsNumeric(Tmp(J)) Then
GetValue = Tmp(J)
Exit For
End If
Next J
End If
End Function5) SAVE and EXIT
6) open it and give it a try.
Pls let me know
gowflow
ASKER
not quite. While it correctly separated batches with the correct amounts per batch, it missed a block of values down further in the list in Col H. The last group it calculated was row 1731 to 1734, however there is a batch from rows 1821 to 1833 it missed.
if it missed something is because of the following:
Col C value either not a date or empty or outside the scoope of the date requested at start of the macro.
Col H value either blank or not a number (typing of some sort)
Pls check your data if it falls in either of the 2 categories above. If not pls post this sheet and I will look at it.
gowflow
Col C value either not a date or empty or outside the scoope of the date requested at start of the macro.
Col H value either blank or not a number (typing of some sort)
Pls check your data if it falls in either of the 2 categories above. If not pls post this sheet and I will look at it.
gowflow
ASKER
Please see attached screenshot. This is the section it missed. I input the date 10/25/2011.
You can see the date in Col C is after that and Col H has data in it.
You can see the date in Col C is after that and Col H has data in it.
IU see nothin unusual. Can you post the worksheet only this worksheet
gowflow
gowflow
Wait I think I found it. Is it the last batch ? I mean the last batch that hv data in Col M
gowlfow
gowlfow
ok sorry my mistake. Was not testing the lower limit here it is. Pls delete the present sub UpdateWUBalance in Module1 and replace it by the below code.
gowflow
gowflow
Sub UpdateWUBalance(ByVal StartDate As Date)
Dim WS As Worksheet
Dim WSWUBalance As Worksheet
Dim I As Long, J As Long
Dim MaxRow As Long, MaxRowWUBalance As Long
Dim Rng As Range
Dim TConfirmed As Double, TColI As Double, TColH As Double, TColG As Double
Dim WUFile As String, WUDate As String
Dim C As Range
Dim Tmp
Dim FirstAddress As String
Dim FoundIt As Boolean
'Update WU-Balance-New
Set WS = Sheets("WU-Staging-FBME")
Set WSWUBalance = Sheets("WU-Balance-New")
MaxRow = WS.Rows(WS.Rows.Count).End(xlUp).Row + 1
MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
'>> 1 >> Filter WU-Staging-FBME for dates > Oct 24, 2011
'Old way of Filtering Prior to 25/11/2011
'WS.UsedRange.AutoFilter Field:=10, Criteria1:=">=" & DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
'WS.UsedRange.AutoFilter Field:=9, Criteria1:=">=" & 0
'New way of Filtering After 25/11/2011
WS.UsedRange.AutoFilter Field:=3, Criteria1:=">=" & DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
WS.UsedRange.AutoFilter Field:=8, Criteria1:=">=" & 0
TColG = 0
TColH = 0
TColI = 0
WUDate = ""
WUFile = ""
'>> 2 >> Add Col I
For I = 2 To MaxRow + 1
If WS.Range(I & ":" & I).EntireRow.Hidden = False Then
If WUDate = "" Then
WUDate = WS.Cells(I, "C")
'Old Way prior to 25/11/2011
'WUFile = WS.Cells(I, "P")
'New Way After 25/11/2011
WUFile = WS.Cells(I, "M")
End If
'Old Way prior to 25/11/2011
'If WS.Cells(I, "P") <> "" Then
'New Way After 25/11/2011
'If WS.Cells(I, "M") <> "" Then
If WS.Cells(I, "M") = WUFile And WS.Cells(I, "C") = WUDate Then
'Old Way prior to 25/11/2011
'TColI = TColI + WS.Cells(I, "I")
'New Way After 25/11/2011
TColH = TColH + GetValue(WS.Cells(I, "H"))
TColG = TColG + GetValue(WS.Cells(I, "G"))
Else
'Lookfor WUDate in sheet 'WU-Balance-New
'Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True)
FoundIt = False
With WSWUBalance.UsedRange
Set C = .Find(What:=WUDate, LookIn:=xlFormulas, lookat:=xlWhole)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If WSWUBalance.Range("B" & C.Row) = WUFile Then
FoundIt = True
Exit Do
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If
End With
If FoundIt Then
'WUDate already there
WSWUBalance.Cells(C.Row, "A") = WUDate
WSWUBalance.Cells(C.Row, "B") = WUFile
WSWUBalance.Cells(C.Row, "C") = TColG
WSWUBalance.Cells(C.Row, "D") = TColH
WSWUBalance.Cells(C.Row, "E").Formula = "=IF(D" & C.Row & "-C" & C.Row & "<>0,D" & C.Row & "-C" & C.Row & ","""")"
Else
'WUDate not there
WSWUBalance.Cells(MaxRowWUBalance, "A") = WUDate
WSWUBalance.Cells(MaxRowWUBalance, "B") = WUFile
WSWUBalance.Cells(MaxRowWUBalance, "C") = TColG
WSWUBalance.Cells(MaxRowWUBalance, "D") = TColH
WSWUBalance.Cells(MaxRowWUBalance, "E").Formula = "=IF(D" & MaxRowWUBalance & "-C" & MaxRowWUBalance & "<>0,D" & MaxRowWUBalance & "-C" & MaxRowWUBalance & ","""")"
WSWUBalance.Cells(MaxRowWUBalance, "H").Formula = "=D" & MaxRowWUBalance & "*0.5%"
WSWUBalance.Cells(MaxRowWUBalance, "J").Formula = "=F" & MaxRowWUBalance & "+H" & MaxRowWUBalance & "-D" & MaxRowWUBalance
'Calculate Cumulative Totals
If MaxRowWUBalance = 2 Then
WSWUBalance.Cells(MaxRowWUBalance, "I").Formula = "=H" & MaxRowWUBalance
WSWUBalance.Cells(MaxRowWUBalance, "K").Formula = "=J" & MaxRowWUBalance
Else
WSWUBalance.Cells(MaxRowWUBalance, "I").Formula = "=H" & MaxRowWUBalance & "+I" & MaxRowWUBalance - 1
WSWUBalance.Cells(MaxRowWUBalance, "K").Formula = "=J" & MaxRowWUBalance & "+K" & MaxRowWUBalance - 1
End If
End If
'Re-Calculate new maxrow and new WUDate
MaxRowWUBalance = WSWUBalance.Rows(WSWUBalance.Rows.Count).End(xlUp).Row + 1
'Old Way prior to 25/11/2011
'WUDate = WS.Cells(I, "C")
'WUFile = WS.Cells(I, "P")
'TColI = WS.Cells(I, "I")
'New Way After 25/11/2011
WUDate = WS.Cells(I, "C")
WUFile = WS.Cells(I, "M")
TColH = GetValue(WS.Cells(I, "H"))
TColG = GetValue(WS.Cells(I, "G"))
End If
'End If
End If
Next I
WS.ShowAllData
WS.AutoFilterMode = False
WSWUBalance.Range("B:B").HorizontalAlignment = xlLeft
WSWUBalance.Columns(2).AutoFit
MsgBox ("Balance Amounts Updated successfully.")
End SubASKER
That seemed to work, however, it looks like I gave you the wrong percentage for Col H in WU-Balance-New. It should be 3.25% not .5%. Sorry. I can change it in the code if you point out where to correct it.
ASKER
And, I need to allow for a beginning balance of -$10,100.00 and if I insert that amount in a Row 2, Col K, when I run the code again it overwrites that value.
Could you start (and keep) K2 having the value of -10,100.00 with the formula in K3 being =J3+K2 ?
Could you start (and keep) K2 having the value of -10,100.00 with the formula in K3 being =J3+K2 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It is 3.25% not 2.25% so I changed the below line in your code to:
WSWUBalance.Cells(MaxRowWU
Great work, gowflow! Been wanting this ability for a long time. Glad gowflow was able to nail it for me.
Thank you!
WSWUBalance.Cells(MaxRowWU
Great work, gowflow! Been wanting this ability for a long time. Glad gowflow was able to nail it for me.
Thank you!
Although I rmember typing 3.25 .... but must hv been after the maraton I ran today ... I was seeing triple !!!
Glad I could help. Pls keep throwing question and I hope we can get threads to below 10 !! ! day !
gowflow
Glad I could help. Pls keep throwing question and I hope we can get threads to below 10 !! ! day !
gowflow
ASKER
Marathon! Wow! How long? From where to where? What was the temperature? Any pictures of the even on a website?
yes well I hv 3 kids so not a youngster we ran the 5K there is all ages all run 1 run w mom, 5,10 and the 42k sorry no images on web.
gowflow
gowflow
Is it ok for :
Input manually the amount that you get in the email ?
lets start with this one
gowflow