• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

improve previous solution

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
JaseSt
Asked:
JaseSt
  • 10
  • 7
  • 2
2 Solutions
 
gowflowCommented:
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
 
Robberbaron (robr)Commented:
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
 
JaseStAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
gowflowCommented:
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
 
Robberbaron (robr)Commented:
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
 
JaseStAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
Any news?
gowflow
0
 
JaseStAuthor Commented:
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
 
gowflowCommented:
ok np
gowflow
0
 
JaseStAuthor Commented:
again, sorry for the delay. It works, but imports Col B data as scientific notation. Can that be fixed?data imported
0
 
gowflowCommented:
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
 
JaseStAuthor Commented:
Perfecto! Thank you very much.
0
 
gowflowCommented:
your welcome.
gowflow
0
 
gowflowCommented:
Let me know if more help is needed.
gowflow
0
 
JaseStAuthor Commented:
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
 
gowflowCommented:
Why do we keep going in circles ? we make a solution then we find out that is it not working correctly ?
gowflow
0
 
gowflowCommented:
ok
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 10
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now