Solved

improve previous solution

Posted on 2013-01-14
19
204 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

757 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

21 Experts available now in Live!

Get 1:1 Help Now