Link to home
Start Free TrialLog in
Avatar of JaseSt
JaseStFlag for United States of America

asked on

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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

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
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.
Avatar of JaseSt

ASKER

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
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
SOLUTION
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JaseSt

ASKER

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.
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
Any news?
gowflow
Avatar of JaseSt

ASKER

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.
ok np
gowflow
Avatar of JaseSt

ASKER

again, sorry for the delay. It works, but imports Col B data as scientific notation. Can that be fixed?User generated image
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JaseSt

ASKER

Perfecto! Thank you very much.
your welcome.
gowflow
Let me know if more help is needed.
gowflow
Avatar of JaseSt

ASKER

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.
Why do we keep going in circles ? we make a solution then we find out that is it not working correctly ?
gowflow