[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

summing values and adding conversion USD to Euros - continued from previous question

Posted on 2011-10-28
21
Medium Priority
?
408 Views
Last Modified: 2012-05-12
A button on the WU-Staging-FBME sheet so that when clicked does this:

1. Sums the individual batch amounts in Col H and inserts that total in Col i of the top row of that batch only for those where Col H has a value for each record in the batch. (A "batch" is the batch of records brought in from a single email. See attached.)  batch examples
If any row in a single batch has an empty value in Col H that means it has yet to be picked up by our receiver and therefore the batch can receive no further processing until all rows in the batch have SOME value.

2. When Col i is filled in it then should kick off the code that is already there, however - and here is a tricky part - I need this formula to instead of putting 1.4 in Col U to look up and input the current USD to Euro conversion rate. Is that possible?

The code that kicks off calculations and input for Cols T - Z is located in Sheet2 (WU-Staging-FBME). It can be removed and replaced with your code if you wish, of course.

Furthermore, the button should only effect those batches where Col i is filled in AND where Cols T-Z are NOT filled in already.

Thank you.
0
Comment
Question by:JaseSt
  • 11
  • 9
21 Comments
 
LVL 8

Expert Comment

by:ragnarok89
ID: 37046676
Looking at your screenshot, it seems that batches are separated by blank lines, is this correct?

Could you attach the excel file in question, and I can build it for you.

Al
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37047829
Note in the 2nd block 5795 on the first row although the 3rd row got zero and you mentioned
If any row in a single batch has an empty value in Col H that means it has yet to be picked up by our receiver and therefore the batch can receive no further processing
>>> I gather that if any row in a batch has either blank or zero then don't put the total on first row as we do not want to see the processing from coil T to Z filled
Is my understanding correct ?? if yes why did you put 5795 in that first row ? If not then explain what do you mean by that means it has yet to be picked up by our receiver and therefore the batch can receive no further processing  ???

gowflow
0
 

Author Comment

by:JaseSt
ID: 37047886
Anything other than a blank in Col H for a batch indicates it can be processed because if I put a zero or he puts feedback in the form of text, it means that that specific Western Union wire cannot be picked up and will have to be submitted again (with a different MTCN number)

There will be (or should be) no blank cells in Cols A - G. Only in H will we see either a blank (which means he did not pick it up) or text telling me there was something wrong with the wire and it will have to be picked up later (and sent again by the sender).

5795 is the sum of that specific batch.

If he sends back the spreadsheet with a blank value in Col H then the batch it is associated with cannot be processed until he sends me another spreadsheet with the specific cell in Col H filled in.

Clearer? If not, keep asking.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 31

Expert Comment

by:gowflow
ID: 37049127
yes for me blank or 0 would mean the same but seems for you blank is not picked up then do not calculate the batch total as still incomplete and zero means he picked it up but unpaid means batch is complete reason why we see the 5795

will revert case any other issues
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37049379
I am still not clear on how to find batches. take this scenario

You have in your workbook 1500 lines in col H you have scattered amounts some follows some have blanks in between, furthermore in the bfirst 1000 lines you have no blank line separating the batches thereafter you have blank lines separating the batches moreover you have in Col I you have values in the beginning of the file that shows the total to that line (say line 50 show a total in Col I and the formula shows SUM(H45:H50) then further down you have batches that shows a formula where in the batch there is blank value in col H

So basically you have a total mixture in the filw of some old data + combined + now you need to set up a new formula

My challenge is
That you want to have a button that each time you clik it wil .... look for batches and sum the batches if complete etc ....

so the problem is where to look for these batches as no specific criteria if I say blank row then I will pick up the first couple of lines and more down ... if I say the last formula in Col I it is at line 1318 in my file but there ater there are couple of batches with no blank lines so what diffrentiate a batch from an other the numbering that start from 1 i figured in Col A but then after I saw some of your formulas ignoring this and stopping just in the middle of numbering like line 58 and still the numbering would continue down

So you need to tell me how to sort all this out !!!!
gowflow
0
 

Author Comment

by:JaseSt
ID: 37050099
yes, when I first started using this page I used different formulas and then over time evolved to where I am today. To make things easier I can delete everything from before June 1st (as an arbitrary date) as I have copies of the Visa workbook that I can use as an archive.

The blank yellow highlighted lines serve to breakup the different batches but we can do something else if that will help you. In other words, you tell me what you need to have done to make it work and we can determine the best solution. This sheet does not have to be in a certain format or layout.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37050639
ok no problem, anyway, like I always do talking about the problem in details help me find the solution which I am testing now. I should hv something ready soon. As soon as we get the total batch what should we do then you mention Code T to Z kicks off ... I doubt it will do as when I input something in I it does not kick off anything ! will check it. But pls confirm that from me or objective of this question is to get the total batches if anything else please detail it here.
gowflow
0
 

Author Comment

by:JaseSt
ID: 37050965
The way the code stands right now is once I sum up the values from Col H into Col i the code populates columns T through Z, but yes, this question is to do just that and if it doesn't kickoff the function to do the calculations and population of T thru Z it needs to:

2. When Col i is filled in it then should kick off the code that is already there, however - and here is a tricky part - I need this formula to instead of putting 1.4 in Col U to look up and input the current USD to Euro conversion rate. Is that possible?

Remember, that way it has kicked off the code to populate A - Z is by summing H and inserting the total in i, then copying and pasting the values from H back into H - that's when it works. This is a part I was hoping you could fix as it is rather cumbersome.

Thank you!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37051096
Noted but pls explain what do you mean by:
1.4 in Col U to look up and input the current USD to Euro conversion rate. ????

To lookup the internet for the rate automatically ? to lookup where ?
gowflow
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 37051265
Here is what I got so far
1) Completed batches will get total of all items in col H put in the first item in Col I that will kick off the existing code 'AS IS' in col U - Z
2) Not Completed batches will see nothing happeneing.
3) All data old intermediate and new with blank rows and without blank rows between emails is compatible with this version. (I concider a batch either of the following:
 A - an item that start by 1 in Col A with no blank rows in between is a delimiter for a new row
 B - Any set of rows that are delimited by a blank row is also concidered a batch

At time of posting this solution I didn't see your reply on the eur conversion of 1.4 to be today's rate !!!

To apply it
1) Make a copy of the latest Visa workbook and name it diffrently.
2) copy the below code and place it in Module1
 
 
Sub SumUpCompletedBatches()
Dim WS As Worksheet
Dim MaxRow As Long, MinRow As Long, I As Long, J As Long
Dim BegBatchRow As Long, EndBatchRow As Long
Dim LastFormula As String
Dim BatchComplete As Boolean

Set WS = ActiveSheet
MinRow = WS.Columns("I").Rows(WS.Rows.Count).End(xlUp).Row
MaxRow = WS.UsedRange.Rows.Count

'Find the Beginning of the first Batch
LastFormula = WS.Range("I" & MinRow).Formula
MinRow = WS.Range(Mid(LastFormula, InStr(1, LastFormula, ":") + 1, Len(LastFormula) - InStr(1, LastFormula, ":") - 1)).Row + 1

For I = MinRow To MaxRow
    'Look for End Batch
    BegBatchRow = I
    J = BegBatchRow
    Do While J + 1 < MaxRow And WS.Range("A" & J + 1) <> "" And WS.Range("A" & J + 1) <> 1
        J = J + 1
    Loop
    If J + 1 >= MaxRow Or WS.Range("A" & J + 1) = "" Then EndBatchRow = J + 1
    If WS.Range("A" & J + 1) = 1 Then EndBatchRow = J
    
    'Locate if Batch Complete or Not
    BatchComplete = True
    For J = BegBatchRow To EndBatchRow
        If WS.Range("H" & J) = "" And WS.Range("A" & J) <> "" Then
            BatchComplete = False
            Exit For
        End If
    Next J
    
    If BatchComplete Then
        WS.Range("I" & BegBatchRow).Formula = "=SUM(H" & BegBatchRow & ":H" & EndBatchRow & ")"
    End If
    
    MinRow = EndBatchRow
    BegBatchRow = MinRow
    EndBatchRow = 0
    I = MinRow
    
Next I

MsgBox ("Sum of Complete Batches successfully ended.")
End Sub

Open in new window


3) SAVE the workbook
4) doubleclick on Sheet2 WU-Staging-FBME in VBA in the left pane.
5) click on the lower left icon in the code window to display each su at a time.
6) Delete the code that is displayed concerning worksheet_change event
7) SELECT ALL in the below window and right click COPY and paste in the code window that you just deleted.

 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range, aCell As Range
    Dim I As Long
    
    Application.EnableEvents = False
    
    On Error GoTo Err
    If Not Intersect(Target, Columns(9)) Is Nothing Then
        
        For Each Rng In Range(Target.Address)
            If Rng.Column = 9 Then
                I = Range(Rng.Address).Row
                Range("U" & I).Formula = "=" & Rng.Address
                Exit For
            End If
        Next Rng
        
        If I = 0 Then Exit Sub
        Range("V" & I).Value = 1.4
        Range("W" & I).Formula = "=" & Range("V" & I).Address & "*5%"
        Range("X" & I).Formula = "=" & Range("V" & I).Address & "+" & Range("W" & I).Address
        Range("Y" & I).Formula = "=" & Range("U" & I).Address & "/" & Range("X" & I).Address
        Range("Z" & I).Formula = "=" & Range("Y" & I).Address & "*7.5%"
        Range("T" & I).Formula = "=IF(Y" & I & "*7.5%<75, Y" & I & "-75, Y" & I & "-(Y" & I & "*7.5%))"
        Range("S" & I).Value = "EUR"
        
    End If
    Application.EnableEvents = True
    Exit Sub
Err:
    If Err.Number <> 1004 Then MsgBox Err.Description
    Application.EnableEvents = True
End Sub

Open in new window


8) SAVE the workbook
9) goto Excel worksheets and click on the sheet WU-Staging-FBME make sure that in the Developper menu Design icon is clicked and press on The Insert Icon on its left and choose from the Active X the commandbox which is the square box and drwa it in Col I like the attached image. Right click on hte button choose property and change its caption to : SUM-UP Completed batches

create button
10) Double click on the button created and in the code window type : SumUpCompletedBatches
11) SAVE the workbook and Exit
12) load it and try it.

Let me know.
gowflow
0
 

Author Comment

by:JaseSt
ID: 37051450
Tried it and it worked great! Even kicked off the code to populate Cols T - Z - so that is good.

However, it did one strange thing, attached, where it put the sum ($295) of a one-entry batch in a blank row above where it should have been. Sum 1

But, in a single-entry batch below it (also attached: $505) the sum was put in the correct row.

Maybe because the $295 sum was the first it had to calculate of the batches? Sum 2
0
 

Author Comment

by:JaseSt
ID: 37051464
If you can fix the problem (slight as it is) of putting the first calculation in the empty row I will close this question and pose the request to have the USD to Euro conversion rate in Col V part of the question as a different question.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37052037
let me get this right. the 295 that came in the yellow line have a 1 in Col A ? and what about the 505 that came correctly what is its line number in Col A ?
gowflow
0
 

Author Comment

by:JaseSt
ID: 37053079
no, the 295 had a 17 in Col A as it was a continuation of previous batches not yet sent to Joseph.

The 505 had a 1 in Col A.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37053920
What did the yellow line had in Col A ?? click on it and make sure there was no blank space or anything else. I tried every combination but all came ok could not reproduce your problem.
gowflow
0
 

Author Comment

by:JaseSt
ID: 37054116
it was blank, but okay. let's just go with it and if there is a problem again, I'll let you know.
0
 

Author Closing Comment

by:JaseSt
ID: 37054122
Great work as always, gowflow. Really appreciate it.

Now onto the currency conversion part of the question. Will post a little later and let you know here
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37054123
Yes coz I could not reproduce no matter what I did. I hope this line you did not delete it or create it manually !!! Anyway hope it is to your satissfaction (beside this not found point !)
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37054126
No need to waist time on the currency conversion in explaining just put a small line and you will get what you want !!! :)
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37057864
Did you post the question ?
gowflow
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question