We help IT Professionals succeed at work.

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
Comment
Watch Question

gowflowPartner
CERTIFIED EXPERT

Commented:
Back to biz ...
Is it ok for :

Input manually the amount that you get in the email ?
lets start with this one
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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(ByVal Target As Range)

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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
"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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.....

gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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
 

Author

Commented:
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?
gowflowPartner
CERTIFIED EXPERT

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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

 
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 Sub

Open in new window


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
gowflowPartner
CERTIFIED EXPERT

Commented:
In my precious post delete Sub ImportWUConfirmedAmts not Sub ImportWUConfirmedAmounts as this Sub doesn't exist.
gowflow

Author

Commented:
"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.

Author

Commented:
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.

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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?
gowflowPartner
CERTIFIED EXPERT

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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.

 
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 Sub

Open in new window


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.

 Update button Image
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 SDate

Open in new window


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

Author

Commented:
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....
gowflowPartner
CERTIFIED EXPERT

Commented:
ok

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
ok go ahead and post it but first is it related to visa/mastercard files or it has nothignto do with it ?
gowflow

Author

Commented:
Great! This graph project uses the Visa workbook and the Mastercard workbook you have.

here it is:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27433750.html
gowflowPartner
CERTIFIED EXPERT

Commented:
Why can't u test the file like I proposed ??
gowflow

Author

Commented:
I'll get back to this one today

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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
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 If

Open in new window

Author

Commented:
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?
gowflowPartner
CERTIFIED EXPERT

Commented:
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

 Insert Col in E
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.

 Format cell to Double digit red
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 Sub

Open in new window


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

Author

Commented:
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.


 Miscalculation 1  Miscalculation 2 Summary
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.



       
gowflowPartner
CERTIFIED EXPERT

Commented:
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

 
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 Function

Open in new window


5) SAVE and EXIT
6) open it and give it a try.

Pls let me know
gowflow

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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. section missed
gowflowPartner
CERTIFIED EXPERT

Commented:
IU see nothin unusual. Can you post the worksheet only this worksheet
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
Wait I think I found it. Is it the last batch ? I mean the last batch that hv data in Col M
gowlfow
gowflowPartner
CERTIFIED EXPERT

Commented:
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
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 Sub

Open in new window

Author

Commented:
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.

Author

Commented:
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 ?
Partner
CERTIFIED EXPERT
Commented:
ok here it is. Replace current Sub UpdateWUBalance by the following. I left cell K2 empty so you can adjust and fill whatever value yyou want there the rest fill pick up from there. The formating for columns now show all negative values in Red easier to spot visually.
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 & "*2.25%"
                    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("C:F").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
WSWUBalance.Range("H:K").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
WSWUBalance.Range("B:B").HorizontalAlignment = xlLeft
WSWUBalance.Columns(2).AutoFit
MsgBox ("Balance Amounts Updated successfully.")

End Sub

Open in new window

Author

Commented:
It is 3.25% not 2.25% so I changed the below line in your code to:

WSWUBalance.Cells(MaxRowWUBalance, "H").Formula = "=D" & MaxRowWUBalance & "*3.25%"

Great work, gowflow! Been wanting this ability for a long time. Glad gowflow was able to nail it for me.

Thank you!
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Marathon! Wow! How long? From where to where? What was the temperature?  Any pictures of the even on a website?
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.