?
Solved

improve previous solution

Posted on 2013-01-14
19
Medium Priority
?
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 2
19 Comments
 
LVL 31

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 31

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 200 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 31

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 31

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
 
LVL 31

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 31

Accepted Solution

by:
gowflow earned 1800 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 31

Expert Comment

by:gowflow
ID: 38817650
your welcome.
gowflow
0
 
LVL 31

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 31

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
 
LVL 31

Expert Comment

by:gowflow
ID: 38831881
ok
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

752 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