JaseSt
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
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
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.
or just post the code for 'Consolidate' routine that is assigned to the button.
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-F BME")
Set HMFsh = Worksheets("HMF Visa")
Set VGsh = Worksheets("Victor Group")
Set WUsh = Worksheets("WU-Staging-FBM E")
'find first empty row in Visa Consolidated
lRow = xlLastRow("VISA Consolidated") + 1
With VCsh
For I = 1 To xlLastRow("Wire-Staging-FB ME")
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
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-F
Set HMFsh = Worksheets("HMF Visa")
Set VGsh = Worksheets("Victor Group")
Set WUsh = Worksheets("WU-Staging-FBM
'find first empty row in Visa Consolidated
lRow = xlLastRow("VISA Consolidated") + 1
With VCsh
For I = 1 To xlLastRow("Wire-Staging-FB
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
4) SAVE and exit the workbook.
5) Give it a try let me know
gowflow
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
4) SAVE and exit the workbook.
5) Give it a try let me know
gowflow
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
gowflow
'---> 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"
gowflow
Any news?
gowflow
gowflow
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
gowflow
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfecto! Thank you very much.
your welcome.
gowflow
gowflow
Let me know if more help is needed.
gowflow
gowflow
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
gowflow
ASKER
It has to do with this one: https://www.experts-exchange.com/questions/27989319/update-of-previous-solution-for-Mastercard-spreadsheet.html
Here is the new question:
https://www.experts-exchange.com/questions/28012428/slight-tweak-to-previous-solution.html
Here is the new question:
https://www.experts-exchange.com/questions/28012428/slight-tweak-to-previous-solution.html
ok
gowflow