Solved

improve previous solution

Posted on 2013-01-14
19
206 Views
Last Modified: 2013-01-29
A function is activated when I click the "Get Loads for Today" button, on the Visa Consolidated sheet, of the Visa workbook.

Attached is a scrubbed version of the Visa Consolidated page only.

What the Get Loads for Today does is populate Cols A - E in the Visa Consolidated sheet by going thru sheets: Wire-Staging-FBME and WU-Staging-FBME and importing data where the date in Col A (for Wire-Staging-FBME) and the date in Col K (for WU-Staging-FBME) equals today's date.

Problem:

Col B in the Visa Consolidated sheet is filled out from Col D for the Wire-Staging-FBME, however, there is no value from the WU-Staging-FBME sheet to draw from to place in Col B, therefore when data is imported from WU-Staging-FBME, Col B is left blank.

Request:

When data is pulled from sheet WU-Staging-FBME, insert the following into Col B:

Where Col A contains the letters: Jen, insert 100002400455
Where Col A contains the letters: Adam, insert 100002369280
Where Col A contains the letters: Shawn AND the last digits in Col C = 116, insert 100002063041
Where Col A contains the letters: Shawn AND the last digits in Col C = 124, insert 100002044918
VisaConsolidated-scrubbed.xlsm
0
Comment
Question by:JaseSt
  • 10
  • 7
  • 2
19 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 38777217
Is this a new function ? I do not have it nor hv the button. Do you hv the sub ? I don't hv anything that is called or like GetLoadsForToday
gowflow
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 38777709
you need to resubmit your workbook as there are no macros attached.
or just post the code for 'Consolidate' routine that is assigned to the button.
0
 

Author Comment

by:JaseSt
ID: 38778047
Looking at the macro assigned to the button I see the function Consolidate. Here it is:

Sub Consolidate()
    Dim lRow As Long, I As Long
    Dim VCsh As Worksheet, Wish As Worksheet, HMFsh As Worksheet, VGsh As Worksheet, WUsh As Worksheet

    Set VCsh = Worksheets("VISA Consolidated")
    Set Wish = Worksheets("Wire-Staging-FBME")
    Set HMFsh = Worksheets("HMF Visa")
    Set VGsh = Worksheets("Victor Group")
    Set WUsh = Worksheets("WU-Staging-FBME")

    'find first empty row in Visa Consolidated
    lRow = xlLastRow("VISA Consolidated") + 1

    With VCsh
        For I = 1 To xlLastRow("Wire-Staging-FBME")
            If Format(Wish.Cells(I, 1), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                .Cells(lRow, 1) = Wish.Cells(I, 3)    'Cardholder
                .Cells(lRow, 2) = Wish.Cells(I, 4)    'FBME ID
                .Cells(lRow, 3) = Wish.Cells(I, 7)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = Wish.Cells(I, 6)    'Amount
                .Cells(lRow, 6) = Wish.Cells(I, 1)    'Date
                lRow = lRow + 1
            End If
        Next
        For I = 1 To xlLastRow("HMF Visa")
            If Format(HMFsh.Cells(I, 13), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                .Cells(lRow, 1) = HMFsh.Cells(I, 2)    'Cardholder
                .Cells(lRow, 2) = HMFsh.Cells(I, 4)    'FBME ID
                .Cells(lRow, 3) = HMFsh.Cells(I, 5)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = HMFsh.Cells(I, 12)    'Amount
                .Cells(lRow, 6) = HMFsh.Cells(I, 13)    'Date
                lRow = lRow + 1
            End If
        Next
        For I = 1 To xlLastRow("Victor Group")
            If Format(VGsh.Cells(I, 13), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                .Cells(lRow, 1) = VGsh.Cells(I, 2)    'Cardholder
                .Cells(lRow, 2) = VGsh.Cells(I, 4)    'FBME ID
                .Cells(lRow, 3) = VGsh.Cells(I, 5)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = VGsh.Cells(I, 12)    'Amount
                .Cells(lRow, 6) = VGsh.Cells(I, 13)    'Date
                lRow = lRow + 1
            End If
        Next
        For I = 1 To xlLastRow("WU-Staging-FBME")
            If Format(WUsh.Cells(I, 11), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                .Cells(lRow, 1) = WUsh.Cells(I, 13)    'Cardholder
                .Cells(lRow, 2) = WUsh.Cells(I, 17)    'FBME ID
                .Cells(lRow, 3) = WUsh.Cells(I, 18)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = WUsh.Cells(I, 20)    'Amount
                .Cells(lRow, 6) = WUsh.Cells(I, 11)    'Date
                lRow = lRow + 1
            End If
        Next
    End With
End Sub
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38778659
Try this:

1) Make a copoy of your latest Visa file and give it a new name.
2) goto VBA and view 1 sub at a time locate Sub Consolidate and delete it.
3) Paste the below code after any end sub

Sub Consolidate()
    Dim lRow As Long, I As Long
    Dim VCsh As Worksheet, Wish As Worksheet, HMFsh As Worksheet, VGsh As Worksheet, WUsh As Worksheet
    Dim WUColB As String
    
    Set VCsh = Worksheets("VISA Consolidated")
    Set Wish = Worksheets("Wire-Staging-FBME")
    Set HMFsh = Worksheets("HMF Visa")
    Set VGsh = Worksheets("Victor Group")
    Set WUsh = Worksheets("WU-Staging-FBME")

    'find first empty row in Visa Consolidated
    lRow = xlLastRow("VISA Consolidated") + 1

    With VCsh
        For I = 1 To xlLastRow("Wire-Staging-FBME")
            If Format(Wish.Cells(I, 1), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                .Cells(lRow, 1) = Wish.Cells(I, 3)    'Cardholder
                .Cells(lRow, 2) = Wish.Cells(I, 4)    'FBME ID
                .Cells(lRow, 3) = Wish.Cells(I, 7)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = Wish.Cells(I, 6)    'Amount
                .Cells(lRow, 6) = Wish.Cells(I, 1)    'Date
                lRow = lRow + 1
            End If
        Next
        For I = 1 To xlLastRow("HMF Visa")
            If Format(HMFsh.Cells(I, 13), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                .Cells(lRow, 1) = HMFsh.Cells(I, 2)    'Cardholder
                .Cells(lRow, 2) = HMFsh.Cells(I, 4)    'FBME ID
                .Cells(lRow, 3) = HMFsh.Cells(I, 5)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = HMFsh.Cells(I, 12)    'Amount
                .Cells(lRow, 6) = HMFsh.Cells(I, 13)    'Date
                lRow = lRow + 1
            End If
        Next
        For I = 1 To xlLastRow("Victor Group")
            If Format(VGsh.Cells(I, 13), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                .Cells(lRow, 1) = VGsh.Cells(I, 2)    'Cardholder
                .Cells(lRow, 2) = VGsh.Cells(I, 4)    'FBME ID
                .Cells(lRow, 3) = VGsh.Cells(I, 5)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = VGsh.Cells(I, 12)    'Amount
                .Cells(lRow, 6) = VGsh.Cells(I, 13)    'Date
                lRow = lRow + 1
            End If
        Next
        
        
        For I = 1 To xlLastRow("WU-Staging-FBME")
            If Format(WUsh.Cells(I, 11), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                
                .Cells(lRow, 1) = WUsh.Cells(I, 13)    'Cardholder
                
                '---> Col B Determining
                'Where Col A contains the letters: Jen, insert 100002400455
                'Where Col A contains the letters: Adam, insert 100002369280
                'Where Col A contains the letters: Shawn AND the last digits in Col C = 116, insert 100002063041
                'Where Col A contains the letters: Shawn AND the last digits in Col C = 124, insert 100002044918
                If InStr(1, LCase(.Cells(lRow, 1)), "jen") <> 0 Then WUColB = "100002400455"
                If InStr(1, LCase(.Cells(lRow, 1)), "adam") <> 0 Then WUColB = "100002369280"
                If InStr(1, LCase(.Cells(lRow, 1)), "shawn") <> 0 And Right(WUsh.Cells(I, 18), 3) = "116" Then WUColB = "100002063041"
                If InStr(1, LCase(.Cells(lRow, 1)), "shawn") <> 0 And Right(WUsh.Cells(I, 18), 3) = "124" Then WUColB = "100002044918"
                
                .Cells(lRow, 2) = WUColB    'FBME ID
                .Cells(lRow, 3) = WUsh.Cells(I, 18)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = WUsh.Cells(I, 20)    'Amount
                .Cells(lRow, 6) = WUsh.Cells(I, 11)    'Date
                lRow = lRow + 1
            End If
        Next
    End With
End Sub

Open in new window


4) SAVE and exit the workbook.
5) Give it a try let me know

gowflow
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 50 total points
ID: 38781242
only addition i would make is to reset WUColB before new row in case there is no match. otherwise it will use the previous one.
              '---> Col B Determining
             WUColB = ""   'blank OR use WUColB="????"  to clearly note no match
                'Where Col A contains the letters: Jen, insert 100002400455

Open in new window

0
 

Author Comment

by:JaseSt
ID: 38781340
okay, I put in gowflow's code and will run it when I have need to which should be pretty soon. I didn't include robberbaron's snippet but will if gowflow thinks it's a good addition. And will give points to him if so.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38781483
Yes absolutely. It was an oversight of me the para should be

'---> Col B Determining
                'Where Col A contains the letters: Jen, insert 100002400455
                'Where Col A contains the letters: Adam, insert 100002369280
                'Where Col A contains the letters: Shawn AND the last digits in Col C = 116, insert 100002063041
                'Where Col A contains the letters: Shawn AND the last digits in Col C = 124, insert 100002044918
               WUColB=""
               If InStr(1, LCase(.Cells(lRow, 1)), "jen") <> 0 Then WUColB = "100002400455"
                If InStr(1, LCase(.Cells(lRow, 1)), "adam") <> 0 Then WUColB = "100002369280"
                If InStr(1, LCase(.Cells(lRow, 1)), "shawn") <> 0 And Right(WUsh.Cells(I, 18), 3) = "116" Then WUColB = "100002063041"
                If InStr(1, LCase(.Cells(lRow, 1)), "shawn") <> 0 And Right(WUsh.Cells(I, 18), 3) = "124" Then WUColB = "100002044918"

Open in new window


gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38797229
Any news?
gowflow
0
 

Author Comment

by:JaseSt
ID: 38797542
sorry for the delay but I'm waiting for my WU guy to send me back the sheet I send him so I can test this function out.  Should be any day now.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:gowflow
ID: 38797770
ok np
gowflow
0
 

Author Comment

by:JaseSt
ID: 38814171
again, sorry for the delay. It works, but imports Col B data as scientific notation. Can that be fixed?data imported
0
 
LVL 29

Accepted Solution

by:
gowflow earned 450 total points
ID: 38815085
ok try this

1) Make a copoy of your latest Visa file and give it a new name.
2) goto VBA and view 1 sub at a time locate Sub Consolidate and delete it.
3) Paste the below code after any end sub

Sub Consolidate()
    Dim lRow As Long, I As Long
    Dim VCsh As Worksheet, Wish As Worksheet, HMFsh As Worksheet, VGsh As Worksheet, WUsh As Worksheet
    Dim WUColB As String
    
    Set VCsh = Worksheets("VISA Consolidated")
    Set Wish = Worksheets("Wire-Staging-FBME")
    Set HMFsh = Worksheets("HMF Visa")
    Set VGsh = Worksheets("Victor Group")
    Set WUsh = Worksheets("WU-Staging-FBME")

    'find first empty row in Visa Consolidated
    lRow = xlLastRow("VISA Consolidated") + 1

    With VCsh
        For I = 1 To xlLastRow("Wire-Staging-FBME")
            If Format(Wish.Cells(I, 1), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                .Cells(lRow, 1) = Wish.Cells(I, 3)    'Cardholder
                .Cells(lRow, 2) = Wish.Cells(I, 4)    'FBME ID
                .Cells(lRow, 3) = Wish.Cells(I, 7)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = Wish.Cells(I, 6)    'Amount
                .Cells(lRow, 6) = Wish.Cells(I, 1)    'Date
                lRow = lRow + 1
            End If
        Next
        For I = 1 To xlLastRow("HMF Visa")
            If Format(HMFsh.Cells(I, 13), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                .Cells(lRow, 1) = HMFsh.Cells(I, 2)    'Cardholder
                .Cells(lRow, 2) = HMFsh.Cells(I, 4)    'FBME ID
                .Cells(lRow, 3) = HMFsh.Cells(I, 5)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = HMFsh.Cells(I, 12)    'Amount
                .Cells(lRow, 6) = HMFsh.Cells(I, 13)    'Date
                lRow = lRow + 1
            End If
        Next
        For I = 1 To xlLastRow("Victor Group")
            If Format(VGsh.Cells(I, 13), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                .Cells(lRow, 1) = VGsh.Cells(I, 2)    'Cardholder
                .Cells(lRow, 2) = VGsh.Cells(I, 4)    'FBME ID
                .Cells(lRow, 3) = VGsh.Cells(I, 5)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = VGsh.Cells(I, 12)    'Amount
                .Cells(lRow, 6) = VGsh.Cells(I, 13)    'Date
                lRow = lRow + 1
            End If
        Next
        
        
        For I = 1 To xlLastRow("WU-Staging-FBME")
            If Format(WUsh.Cells(I, 11), "mm/dd/yy") = Format(Now, "mm/dd/yy") Then
                
                .Cells(lRow, 1) = WUsh.Cells(I, 13)    'Cardholder
                WUColB = ""
                '---> Col B Determining
                'Where Col A contains the letters: Jen, insert 100002400455
                'Where Col A contains the letters: Adam, insert 100002369280
                'Where Col A contains the letters: Shawn AND the last digits in Col C = 116, insert 100002063041
                'Where Col A contains the letters: Shawn AND the last digits in Col C = 124, insert 100002044918
                If InStr(1, LCase(.Cells(lRow, 1)), "jen") <> 0 Then WUColB = "100002400455"
                If InStr(1, LCase(.Cells(lRow, 1)), "adam") <> 0 Then WUColB = "100002369280"
                If InStr(1, LCase(.Cells(lRow, 1)), "shawn") <> 0 And Right(WUsh.Cells(I, 18), 3) = "116" Then WUColB = "100002063041"
                If InStr(1, LCase(.Cells(lRow, 1)), "shawn") <> 0 And Right(WUsh.Cells(I, 18), 3) = "124" Then WUColB = "100002044918"
                .Cells(lRow, 2).NumberFormat = "################"
                .Cells(lRow, 2) = WUColB    'FBME ID
                .Cells(lRow, 3) = WUsh.Cells(I, 18)    'Card Number
                .Cells(lRow, 4) = "EUR"
                .Cells(lRow, 5) = WUsh.Cells(I, 20)    'Amount
                .Cells(lRow, 6) = WUsh.Cells(I, 11)    'Date
                lRow = lRow + 1
            End If
        Next
    End With
End Sub

Open in new window


4) SAVE and exit the workbook.
5) Give it a try let me know

gowflow
0
 

Author Closing Comment

by:JaseSt
ID: 38817465
Perfecto! Thank you very much.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38817650
your welcome.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38822594
Let me know if more help is needed.
gowflow
0
 

Author Comment

by:JaseSt
ID: 38824977
yes I do have one, thanks. Remember the last work you did on the Mastercard MCR sheets about not adding rows? Something isn't working right. I'll post a question about it soon. Thanks.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38825765
Why do we keep going in circles ? we make a solution then we find out that is it not working correctly ?
gowflow
0
 

Author Comment

by:JaseSt
ID: 38830843
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38831881
ok
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
First Blank Cell in a range 7 35
time format showing wrong 12 51
Search for a value in Column? 5 21
Dynamic Filter ? 4 21
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now