gowflow
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
'---> Col B Determining
WUColB = "" 'blank OR use WUColB="????" to clearly note no match
'Where Col A contains the letters: Jen, insert 100002400455
'---> 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"
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
If you are experiencing a similar issue, please ask a related question
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 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
23 Experts available now in Live!