Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Part 10 to: Import more data into spreadsheet

Posted on 2013-06-17
46
Medium Priority
?
277 Views
Last Modified: 2013-07-10
Continuing on from: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28159139.html

Now need the following function:

WHEN a card number  beginning with 5116 is inserted in Col T of Applicant Status AND when I insert a value into Col Q (Approved - note: its value turns to a date when I put an x in that column) do the following:

1. Locate the 5116 full card number (from Applicant Status.xls - Col T) in the spreadsheet Proxy_Numbers.xls Col C (Cardnumber - path: C:\Users\Michael\Dropbox\Sovereign)

2. Copy the Proxy Number from that spreadsheet (from Col B - Proxy_Numbers.xls )

3. Create a email, addressing it to the email addresses (plural if more than one) found in Col D of Applicant Status.xls

4. Subject: "Proxy Number for Sovereign USD Mastercard"

5.  Body (without quotes):

"Dear {first name from Col A, Applicant Status.xls}

Your card must be activated first before you can access your online account.
To activate it I must first tell the bank to do so  which I have just done.  However,
it often takes them till the end of the day to activate your account.

If after you follow the below instructions and you still can't access your account,
please try again later in the day.

Below are the instructions for setting up your online Mastercard bank account.

Please click on the following link: https://www.pcbmyaccount.com/index.cfm.

When there for the first time please click 'Sign Up' for establishing your online account management. You will then be taken to a screen and asked for a 4 digit number.

First try 4524. If that doesn’t work use the last 4 telephone digits you provided.

Then enter your proxy number which is: {enter proxy number from Col B, Proxy_number.xls here}

You are then ready to choose your username, password and security questions.

If you have any problems setting up your online account please feel free to contact me.

In a subsequent email I will send you card loading instructions.


Michael
Sovereign Goldcard Support Services"

6. After the sentence above where it says: "Then enter your proxy number which is:"
enter the Proxy Number from Col B, Proxy_Numbers.xls

Scrubbed example of Proxy Numbers attached.

Scrubbed-Proxy-Numbers.xls
0
Comment
Question by:JaseSt
  • 23
  • 23
46 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39254256
Disn't you forgot to mention to save the proxy in Col W ??
gowflow
0
 

Author Comment

by:JaseSt
ID: 39254296
Oops! You're beating me at my own game!! You're right. Thanks
for noticing. Yes, please do that.

Now, just to have you be prepared, I'm going to ask for a similar function so that when I put in a value into the column Activated (Col R) it will initiate another email. But for now, yes please import that Proxy number into Col W. And if it can't find the proxy number (because the card number is wrong) then do what? I guess bring up an error message and allow me to find the proxy number and insert it manually.

Thanks gowflow!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39254333

column Activated (Col R) it will initiate another email


Thought so when I read the email content Iguessed that there was an other one coming down the line.

No problem



And if it can't find the proxy number (because the card number is wrong) then do what? I guess bring up an error message and allow me to find the proxy number and insert it manually.




Well here you have 2 scenarios:
1) the card is Not yet Assigned ...> then presume do nothing
2) there is a card and it start by 5116 then prefer to just state Proxy not found pls check file so and so. As if we keep the file opened and then you need to update it then it will be a mess in the macro. So when you update the file and run it again it should find it.

Pls confirm ok
gowflow
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JaseSt
ID: 39254366
1.  I suppose that is okay. However on the other hand, it may be true that I get notification from the bank the card is approved before I know what the card number is. And I would then put a value under Approved (Col Q). When the customer gets the card they then inform me the card number.

So I guess the best scenario is that initiate the function as long as both parameters are met:
a card number beginning with 5116 in Col T AND a value inserted into Col Q. If both those parameters are not met then give me some notification.

2. Yes, if the card number is wrong and proxy number can't be found, yes just state the proxy number cannot be found. And Yes, when I input the correct number the function runs and finds the proxy number, etc.

Thank you!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39258708
ok here it is it took some intensive testing as we have several options here.

1) Make a copy of your latest file and give it a new name
2) Goto VBA and doublclick on Sheet1 and locate the Sub Worksheet_Change event and delete all the code that is there.
3) Select again this event and it should be like this
=========
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
=========

4) Paste the below code after
Private Sub Worksheet_Change(ByVal Target As Range)


Dim cCell As Range
Dim fName As String

'---> disable all events while in this procedure to prevent from looping
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'---> Update Date in Col O,P,Q,R,S when x is inputed.
For Each cCell In Target
    If (Not Intersect(cCell, Columns("O")) Is Nothing Or _
        Not Intersect(cCell, Columns("P")) Is Nothing Or _
        Not Intersect(cCell, Columns("Q")) Is Nothing Or _
        Not Intersect(cCell, Columns("R")) Is Nothing Or _
        Not Intersect(cCell, Columns("S")) Is Nothing) _
        And LCase(cCell.Value) = "x" Then
        cCell = Format(Now, "mm/dd/yyyy")
    End If
Next cCell


'---> Send Email if Cell in Col N has a value and Cell in Col K
If Not Intersect(Target, Columns("N")) Is Nothing Or Not Intersect(Target, Columns("K")) Is Nothing Then
    If Range("N" & Target.Row) <> "" And _
        Range("K" & Target.Row) <> "" Then
        If MsgBox("Send Mail for " & Cells(Target.Row, "C") & ", " & Cells(Target.Row, "B") & " ?", vbQuestion + vbYesNo, "Send Email") = vbYes Then
            fName = CreateNewCardLoad(Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")))
            SendEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")), fName
            Exit Sub
        End If
    End If

Else
    
    If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") <> 0 Then
        Cells(Target.Row, "W") = GetProxy(Cells(Target.Row, "T"))
        If Cells(Target.Row, "W") <> "" Then
            SendActivationEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T"))
        End If
    Else
        If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") = 0 And Cells(Target.Row, "T") <> "Not Yet Assigned" Then
            MsgBox ("It seems that the credit card number entered " & Cells(Target.Row, "T") & " does not start with '5116' ")
        End If
    End If
    
End If

'---> Re-activate all events prior exit
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

Open in new window


5) SAVE the workbook.
6) Doubleclick on Module1 and after any End sub Paste the below code:

Function GetProxy(CC As String) As String
Dim sProxyFile As String
Dim WS As Worksheet
Dim WB As Workbook
Dim cCell As Range

sProxyFile = "C:\Users\Michael\Dropbox\Sovereign\Proxy-Numbers.xls"
'sProxyFile = ActiveWorkbook.Path & "\Proxy-Numbers.xls"

Set WB = Workbooks.Open(Filename:=sProxyFile)
Set WS = ActiveSheet

Set cCell = WS.UsedRange.Find(what:=CC, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not cCell Is Nothing Then
    GetProxy = cCell.Offset(, -1)
Else
    MsgBox ("This Card Number " & CC & " was not found in Proxy-Numbers.xls")
End If

WB.Close savechanges:=False

'---> Clean Variables
Set WB = Nothing
Set WS = Nothing

DoEvents

End Function



Sub SendActivationEmail(Rng As Range)
Dim SendTo As String
Dim sTo
Dim OutlookApp As Object
Dim MItem As Object
Dim subject_ As String
Dim attach_ As String
Dim omail As Outlook.MailItem


'---> Create Outlook
Set OutlookApp = CreateObject("Outlook.Application")

'---> Fill in Subject Details'
subject_ = "Proxy Number for Sovereign USD Mastercard" ' (" & Rng.Cells(1, "B") & " " & Rng.Cells(1, "C") & ")"

'---> Fill in email addresses
sTo = Split(Rng.Cells(1, "D"), "/")

For I = 0 To UBound(sTo)
    If SendTo <> "" Then SendTo = SendTo & "; "
    SendTo = SendTo & LTrim(RTrim(sTo(I)))
Next I


'---> Create the Email
Set MItem = OutlookApp.CreateItem(0)
With MItem
  .To = SendTo
  .Subject = subject_
  
  .Body = "Dear " & Rng.Cells(1, "B") & Chr(10) & Chr(10) _
    & "Your card must be activated first before you can access your online account." & Chr(10) _
    & "To activate it I must first tell the bank to do so  which I have just done.  However," & Chr(10) _
    & "it often takes them till the end of the day to activate your account." & Chr(10) & Chr(10) _
    & "If after you follow the below instructions and you still can't access your account," & Chr(10) _
    & "please try again later in the day." & Chr(10) & Chr(10) _
    & "Below are the instructions for setting up your online Mastercard bank account." & Chr(10) & Chr(10) _
    & "Please click on the following link: https://www.pcbmyaccount.com/index.cfm." & Chr(10) & Chr(10) _
    & "When there for the first time please click 'Sign Up' for establishing your online account" & Chr(10) _
    & "management. You will then be taken to a screen and asked for a 4 digit number. " & Chr(10) & Chr(10) _
    & "First try 4524. If that doesn’t work use the last 4 telephone digits you provided. " & Chr(10) & Chr(10) _
    & "Then enter your proxy number which is: " & Rng.Cells(1, "W") & Chr(10) & Chr(10) _
    & "You are then ready to choose your username, password and security questions." & Chr(10) & Chr(10) _
    & "If you have any problems setting up your online account please feel free to contact me." & Chr(10) & Chr(10) _
    & "In a subsequent email I will send you card loading instructions." & Chr(10) & Chr(10) _
    & "Michael" & Chr(10) _
    & "Sovereign Gold Card Support Services"
  'Send the Email
  .Display
End With

'Clear Resources
Set MItem = Nothing
Set OutlookApp = Nothing

End Sub

Open in new window


7) SAVE and Exit the workbook.
8) try it with situation where there is a CC that is in proxy file, CC that is not there, a CC that does not start with 5116 and CC that Not Yet Assigned.

Let me know
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39262323
Did you hv a chance to try it ?
gowflow
0
 

Author Comment

by:JaseSt
ID: 39262645
well, a few other problems popped up:

1. when putting an x into Col Q it pops up a Run-time error 1004. It seems it's looking for proxy_numbers.xls in the wrong location. See the1004 attached.

The location of this file is actually here:location
2. there is another problem as well, but let's deal with this one first
0
 

Author Comment

by:JaseSt
ID: 39262717
Also Part 8 to: Import more data into spreadsheet function is not working. Putting a value into Col N with a value in K does nothing.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39263012
For the location of the proxy file:


1. Locate the 5116 full card number (from Applicant Status.xls - Col T) in the spreadsheet Proxy_Numbers.xls Col C (Cardnumber - path: C:\Users\Michael\Dropbox\Sovereign)

It seems that you made a mistake and it should be:
C:\Users\Michael\Dropbox\Sovereign\MasterCard

??? Please confirm.


2. There is no problem for this function on my side maybe you changed the file name it should be:
Blank - New Card Load.xls  (not underscore here like proxy but hyphen)

and the path is:
C:\Users\Michael\Dropbox\Sovereign\MasterCard\MasterCardLoadRequests

Maybe you changed it as well.

check and let me know so I post the changes.
gowflow
0
 

Author Comment

by:JaseSt
ID: 39263040
1. Yes, my mistake. I'm sorry.  The correct path to Proxy_Numbers.xls is:
C:\Users\Michael\Dropbox\Sovereign\MasterCard

2.  The path is correct as I have it too.blank
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39263068
ok then lets try to refresh your code maybe it got ruined with the different versions

1) Make a copy of your latest file and give it a new name
2) Open VBA doubleclick on module1 and delete ALL the code that is there.
3) Paste the below code in Module1

Sub SendActivationEmail(Rng As Range)
Dim SendTo As String
Dim sTo
Dim OutlookApp As Object
Dim MItem As Object
Dim subject_ As String
Dim attach_ As String
Dim omail As Outlook.MailItem


'---> Create Outlook
Set OutlookApp = CreateObject("Outlook.Application")

'---> Fill in Subject Details'
subject_ = "Proxy Number for Sovereign USD Mastercard" ' (" & Rng.Cells(1, "B") & " " & Rng.Cells(1, "C") & ")"

'---> Fill in email addresses
sTo = Split(Rng.Cells(1, "D"), "/")

For I = 0 To UBound(sTo)
    If SendTo <> "" Then SendTo = SendTo & "; "
    SendTo = SendTo & LTrim(RTrim(sTo(I)))
Next I


'---> Create the Email
Set MItem = OutlookApp.CreateItem(0)
With MItem
  .To = SendTo
  .Subject = subject_
  
  .Body = "Dear " & Rng.Cells(1, "B") & Chr(10) & Chr(10) _
    & "Your card must be activated first before you can access your online account." & Chr(10) _
    & "To activate it I must first tell the bank to do so  which I have just done.  However," & Chr(10) _
    & "it often takes them till the end of the day to activate your account." & Chr(10) & Chr(10) _
    & "If after you follow the below instructions and you still can't access your account," & Chr(10) _
    & "please try again later in the day." & Chr(10) & Chr(10) _
    & "Below are the instructions for setting up your online Mastercard bank account." & Chr(10) & Chr(10) _
    & "Please click on the following link: https://www.pcbmyaccount.com/index.cfm." & Chr(10) & Chr(10) _
    & "When there for the first time please click 'Sign Up' for establishing your online account" & Chr(10) _
    & "management. You will then be taken to a screen and asked for a 4 digit number. " & Chr(10) & Chr(10) _
    & "First try 4524. If that doesn’t work use the last 4 telephone digits you provided. " & Chr(10) & Chr(10) _
    & "Then enter your proxy number which is: " & Rng.Cells(1, "W") & Chr(10) & Chr(10) _
    & "You are then ready to choose your username, password and security questions." & Chr(10) & Chr(10) _
    & "If you have any problems setting up your online account please feel free to contact me." & Chr(10) & Chr(10) _
    & "In a subsequent email I will send you card loading instructions." & Chr(10) & Chr(10) _
    & "Michael" & Chr(10) _
    & "Sovereign Gold Card Support Services"
  'Send the Email
  .Display
End With

'Clear Resources
Set MItem = Nothing
Set OutlookApp = Nothing

End Sub

Sub SendEmail(Rng As Range, fName As String)
Dim WB As Workbook
Dim WS As Worksheet


Dim SendTo As String
Dim Blindcc As String
Dim OutlookApp As Object
Dim MItem As Object
Dim subject_ As String
Dim attach_ As String
Dim fFile
Dim omail As Outlook.MailItem

Application.DisplayAlerts = False

'Create Outlook
Set OutlookApp = CreateObject("Outlook.Application")

'Fill in Subject Details'
subject_ = "New Mastercard Application for (" & Rng.Cells(1, "B") & " " & Rng.Cells(1, "C") & ")"
SendTo = "nmai@banking.bz"
Blindcc = "david@offshorelawcenter.com"

'Create the Email
Set MItem = OutlookApp.CreateItem(0)
With MItem
  .To = SendTo
  .BCC = Blindcc
  .Subject = subject_
  
  '---> Attach files
  For Each fFile In Rng.SpecialCells(xlCellTypeFormulas)
        If InStr(1, fFile.Formula, "HYPERLINK") <> 0 Then
            fpos = InStr(1, fFile.Formula, "HYPERLINK") + 11
            attach_ = Mid(fFile.Formula, fpos, InStr(fpos, fFile.Formula, Chr(34)) - fpos)
            .Attachments.Add (attach_)
        End If
  Next fFile
  
  .Attachments.Add (fName)
  .Body = "Hi Nalleli," & Chr(10) & Chr(10) _
    & "Attached are the documents and load request for (" & Rng.Cells(1, "B") & " " & Rng.Cells(1, "C") & ")" & Chr(10) _
    & "Please have his card shipped to address indicated on spreadsheet." & Chr(10) _
    & "PIC:99554Freedom" & Chr(10) & Chr(10) _
    & "Please let me know you received this email." & Chr(10) & Chr(10) _
    & "Thank you." & Chr(10) & Chr(10) _
    & "Michael" & Chr(10) _
    & "Sovereign Gold Card Support" & Chr(10) _
    & "www.sovereigngoldcard.com"

  'Send the Email
  .Display
End With

'Clear Resources
Set MItem = Nothing
Set OutlookApp = Nothing

Application.DisplayAlerts = True
End Sub


Function CreateNewCardLoad(Rng As Range) As String
Dim sPathName As String
Dim sFileName As String
Dim sBlankCardLoad As String
Dim MaxRow As Long
Dim WS As Worksheet
Dim WB As Workbook

sBlankCardLoad = "C:\Users\Michael\Dropbox\Sovereign\MasterCard\MasterCardLoadRequests\Blank - New Card Load.xls"
sPathName = "C:\Users\Michael\Dropbox\Sovereign\MasterCard\MasterCardLoadRequests\"

'sBlankCardLoad = ActiveWorkbook.Path & "\Blank - New Card Load.xls"
'sPathName = ActiveWorkbook.Path & "\"

Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set WB = Workbooks.Open(Filename:=sBlankCardLoad)
Set WS = ActiveSheet
MaxRow = WS.UsedRange.Rows.Count + 1

sFileName = sPathName & Rng.Cells(1, "B") & " " & Rng.Cells(1, "C") & " - New Card Load.xls"

'---> Affect Values to Card Load.xls
WS.Range("D" & MaxRow) = Rng.Cells(1, "T")
WS.Range("H" & MaxRow) = Rng.Cells(1, "B")
WS.Range("I" & MaxRow) = Rng.Cells(1, "C")
WB.SaveAs Filename:=sFileName
CreateNewCardLoad = sFileName
WB.Close savechanges:=True

'---> Clean Variables
Set WB = Nothing
Set WS = Nothing

Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
DoEvents


End Function

Function GetProxy(CC As String) As String
Dim sProxyFile As String
Dim WS As Worksheet
Dim WB As Workbook
Dim cCell As Range

sProxyFile = "C:\Users\Michael\Dropbox\Sovereign\MasterCard\Proxy_Numbers.xls"
'sProxyFile = ActiveWorkbook.Path & "\Proxy_Numbers.xls"

Set WB = Workbooks.Open(Filename:=sProxyFile)
Set WS = ActiveSheet

Set cCell = WS.UsedRange.Find(what:=CC, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not cCell Is Nothing Then
    GetProxy = cCell.Offset(, -1)
Else
    MsgBox ("This Card Number " & CC & " was not found in Proxy_Numbers.xls")
End If

WB.Close savechanges:=False

'---> Clean Variables
Set WB = Nothing
Set WS = Nothing

DoEvents

End Function



Function ProcessCC(CC As String) As String
   
Dim ILength As Integer, I As Integer
Dim CCFormat As String
   

ILength = Len(CC)
For I = 1 To ILength
    If IsNumeric(Mid(CC, I, 1)) And Mid(CC, I, 1) <> " " Then
        CCFormat = CCFormat & Mid(CC, I, 1)
    End If
Next I

ProcessCC = CCFormat

End Function

Open in new window


4) SAVE the workbook.
5) doubleclick on Sheet1 and select to view 1 sub at a time by clicking on the button left icon
6) Select Worksheet_change event and delete all the code that is there
7) Select again worksheet change and paste the below code after the header Worksheet_change

Dim cCell As Range
Dim fName As String

'---> disable all events while in this procedure to prevent from looping
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'---> Update Date in Col O,P,Q,R,S when x is inputed.
For Each cCell In Target
    If (Not Intersect(cCell, Columns("O")) Is Nothing Or _
        Not Intersect(cCell, Columns("P")) Is Nothing Or _
        Not Intersect(cCell, Columns("Q")) Is Nothing Or _
        Not Intersect(cCell, Columns("R")) Is Nothing Or _
        Not Intersect(cCell, Columns("S")) Is Nothing) _
        And LCase(cCell.Value) = "x" Then
        cCell = Format(Now, "mm/dd/yyyy")
    End If
Next cCell


'---> Send Email if Cell in Col N has a value and Cell in Col K
If Not Intersect(Target, Columns("N")) Is Nothing Or Not Intersect(Target, Columns("K")) Is Nothing Then
    If Range("N" & Target.Row) <> "" And _
        Range("K" & Target.Row) <> "" Then
        If MsgBox("Send Mail for " & Cells(Target.Row, "C") & ", " & Cells(Target.Row, "B") & " ?", vbQuestion + vbYesNo, "Send Email") = vbYes Then
            fName = CreateNewCardLoad(Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")))
            SendEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")), fName
            Exit Sub
        End If
    End If

Else
    
    If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") <> 0 Then
        Cells(Target.Row, "W") = GetProxy(Cells(Target.Row, "T"))
        If Cells(Target.Row, "W") <> "" Then
            SendActivationEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T"))
        End If
    Else
        If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") = 0 And Cells(Target.Row, "T") <> "Not Yet Assigned" Then
            MsgBox ("It seems that the credit card number entered " & Cells(Target.Row, "T") & " does not start with '5116' ")
        End If
    End If
    
End If

'---> Re-activate all events prior exit
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

Open in new window


8) SAVE and Exit the workbook
9) open it and try it.

gowflow
0
 

Author Comment

by:JaseSt
ID: 39263583
ok, now I don't get an error, but it doesn't do anything when I put a value in Q with a value in K.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39263620
You never mentioned anything about value in Q and Value in K !!!


WHEN a card number  beginning with 5116 is inserted in Col T of Applicant Status AND when I insert a value into Col Q (Approved - note: its value turns to a date when I put an x in that column) do the following:

No K in the whole question !!!! where did you get this from ? or I am mistaken ??
gowflow
0
 

Author Comment

by:JaseSt
ID: 39263667
Ok, you're right. Tthe value in Col K corresponds to the 5116 number in col T. The 5116 number means that there is a value in Col K.

In any case when I do put a Value in Col Q, nothing happens even though I do have a 5116 number it T and a Value in K.

Let me try reinserting your code and try again.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39263726
it is working perfectly here.

I only hope it is not a 2010 issue !!!
gowflow
0
 

Author Comment

by:JaseSt
ID: 39264107
re-inserted your code and putting a value in Col Q does nothing.
I've attached a scrubbed version of Applicant Status for you to take a look at if that will help.scrubbed-APPLICANT-STATUS-6-20-1.xls
0
 

Author Comment

by:JaseSt
ID: 39264752
hold on, I think it worked. have to double check later
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39266020
let me know. I am not touching the code before I get your feedback as here it is working fine.
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39270575
Any news ?
gowflow
0
 

Author Comment

by:JaseSt
ID: 39271772
not sure where to start - it's not doing some things it did before. Not capturing email address or card number when there is no column headers. Got an email with such an xls spreadsheet.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39273131
can't do miracles !!!
No header = no retrieval

you need to decide on a system if you want automation. You got to get your clients to 'behave'

My suggestion don't fool around or try to twist code to cater for all scenarios, simply stick to your format and go back to client saying: Sorry fill this form and send it back our system only accept THIS FILE !!!

:)
gowflow
0
 

Author Comment

by:JaseSt
ID: 39273144
I agree. And I do that, just wanted to make sure it wasn't an easy fix.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39273182
no forget it no fix for that as you will never finish when you start with this and it will easily backfire on you as you may get conflicting info.

go safe and enforce the rule !
good luck
gowflow
0
 

Author Comment

by:JaseSt
ID: 39274594
still testing this, gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39274597
ok
0
 

Author Comment

by:JaseSt
ID: 39274617
when creating the first_name last_name - New Card Load.xls file it is putting the card number and the first and last name on row 3 instead of row 2. This is from the function created in

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28157691.html

Row 2 DOES have data in that row but not in Col D and Col H and Col i which is where the card number (Col D), last name (Col H) and first name (Col i) are supposed to go.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39274627
as usual you are messed-up with version in that link I gave you the correction to do in ID: 39249580 check it again.
gowflow
0
 

Author Comment

by:JaseSt
ID: 39274708
sorry gowflow, but could you point me to the code? doing a search on that number brings me back to this page.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39274747
In Module1 there is a Sub Called CreateNewCardLoad
change this line
MaxRow = WS.UsedRange.Rows.Count + 1

to be
MaxRow = WS.UsedRange.Rows.Count


gowflow
0
 

Author Comment

by:JaseSt
ID: 39274785
that was it. thank you!

Still testing the proxy number function and it seems to work perfectly however for Col W where it puts the proxy number it formats it to scientific notation. Can that be fixed in this question or do you want me to submit another?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39274859
well it should not put it as scientific unless you put it manually there then yes it turn it to scientific. If you input manually then just click on the cell press F2 then enter and it should fix the problem for that cell.

If the  macro is writing the value of the card it should write it correctly.

Now that you are testing I presume you are entering the values manually or copy/paste
then F2 Enter should do it.

gowflow
0
 

Author Comment

by:JaseSt
ID: 39278038
just checked it again. the proxy number is put in by the code and is going in as scientific notation
0
 

Author Comment

by:JaseSt
ID: 39278040
and hitting f2 shows the full number in the cell but then reverts back to scientific notation
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39278058
Let me look at this. Should revert in 2+ duty time
:)

gowflow
0
 

Author Comment

by:JaseSt
ID: 39287638
this is to the system that says I need to pay attention to this question: I am!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39287835
Sorry totally overlooked this question.

open your file goto vba and doubleclick on sheet1 and display worksheet_change event. Delete all the code that is there. locate worksheet_ change event again and paste below code after the first line Private Worksheet_change

Dim cCell As Range
Dim fName As String

'---> disable all events while in this procedure to prevent from looping
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'---> Update Date in Col O,P,Q,R,S when x is inputed.
For Each cCell In Target
    If (Not Intersect(cCell, Columns("O")) Is Nothing Or _
        Not Intersect(cCell, Columns("P")) Is Nothing Or _
        Not Intersect(cCell, Columns("Q")) Is Nothing Or _
        Not Intersect(cCell, Columns("R")) Is Nothing Or _
        Not Intersect(cCell, Columns("S")) Is Nothing) _
        And LCase(cCell.Value) = "x" Then
        cCell = Format(Now, "mm/dd/yyyy")
    End If
Next cCell


'---> Send Email if Cell in Col N has a value and Cell in Col K
If Not Intersect(Target, Columns("N")) Is Nothing Or Not Intersect(Target, Columns("K")) Is Nothing Then
    If Range("N" & Target.Row) <> "" And _
        Range("K" & Target.Row) <> "" Then
        If MsgBox("Send Mail for " & Cells(Target.Row, "C") & ", " & Cells(Target.Row, "B") & " ?", vbQuestion + vbYesNo, "Send Email") = vbYes Then
            fName = CreateNewCardLoad(Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")))
            SendEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")), fName
            Exit Sub
        End If
    End If

Else
    
    If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") <> 0 Then
        WS.Range("W" & Target.Row).NumberFormat = "@"
        Cells(Target.Row, "W") = GetProxy(Cells(Target.Row, "T"))
        If Cells(Target.Row, "W") <> "" Then
            SendActivationEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T"))
        End If
    Else
        If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") = 0 And Cells(Target.Row, "T") <> "Not Yet Assigned" Then
            MsgBox ("It seems that the credit card number entered " & Cells(Target.Row, "T") & " does not start with '5116' ")
        End If
    End If
    
End If

'---> Re-activate all events prior exit
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

Open in new window


save and close the workbook. Open it and try it.

gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39292486
Any chance to hv tried it out ?
gowflow
0
 

Author Comment

by:JaseSt
ID: 39297767
tried it but got error when I put a card number into Col T.error
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39299279
Ooops

replace this
WS.Range("W" & Target.Row).NumberFormat = "@"

by this
Range("W" & Target.Row).NumberFormat = "@"


gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39305380
Any news ?
gowflow
0
 

Author Comment

by:JaseSt
ID: 39307311
sorry, gowflow. 4th of July events and celebrations have kept me from attending this. I'll get back to you in a few minutes after testing it.
0
 

Author Comment

by:JaseSt
ID: 39307598
It seems to work, but the function also kicks in when I delete a value from Col Q.
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 39308075
ok this should fix the blank cell issue or not to activate when you delete something in Col Q.

replace the code in worksheet_Change event of sheet1 by the below code. Paste the below code after this line
Private Sub Worksheet_Change(ByVal Target As Range)


Dim cCell As Range
Dim fName As String

'---> disable all events while in this procedure to prevent from looping
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'---> Update Date in Col O,P,Q,R,S when x is inputed.
For Each cCell In Target
    If (Not Intersect(cCell, Columns("O")) Is Nothing Or _
        Not Intersect(cCell, Columns("P")) Is Nothing Or _
        Not Intersect(cCell, Columns("Q")) Is Nothing Or _
        Not Intersect(cCell, Columns("R")) Is Nothing Or _
        Not Intersect(cCell, Columns("S")) Is Nothing) _
        And LCase(cCell.Value) = "x" Then
        cCell = Format(Now, "mm/dd/yyyy")
    End If
Next cCell


'---> Send Email if Cell in Col N has a value and Cell in Col K
If Not Intersect(Target, Columns("N")) Is Nothing Or Not Intersect(Target, Columns("K")) Is Nothing Then
    If Range("N" & Target.Row) <> "" And _
        Range("K" & Target.Row) <> "" Then
        If MsgBox("Send Mail for " & Cells(Target.Row, "C") & ", " & Cells(Target.Row, "B") & " ?", vbQuestion + vbYesNo, "Send Email") = vbYes Then
            fName = CreateNewCardLoad(Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")))
            SendEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")), fName
            Exit Sub
        End If
    End If

Else
    
    If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") <> 0 And Range("Q" & Target.Row) <> "" Then
        Range("W" & Target.Row).NumberFormat = "@"
        Cells(Target.Row, "W") = GetProxy(Cells(Target.Row, "T"))
        If Cells(Target.Row, "W") <> "" Then
            SendActivationEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T"))
        End If
    Else
        If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") = 0 And Cells(Target.Row, "T") <> "Not Yet Assigned" And Range("Q" & Target.Row) <> "" Then
            MsgBox ("It seems that the credit card number entered " & Cells(Target.Row, "T") & " does not start with '5116' ")
        End If
    End If
    
End If

'---> Re-activate all events prior exit
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

Open in new window


gowflow
0
 

Author Closing Comment

by:JaseSt
ID: 39314480
Excellent! Thank you, Gowflow.

I have another if ready and willing
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39314766
yes go ahead
gowflow
0

Featured Post

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.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

885 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