Part 9 to: Import more data into spreadsheet
Continuing on from: https://www.experts-exchange.com/questions/28157691/Part-8-to-Import-more-data-into-spreadsheet.html
Now needing the card number imported from Col T Applicant Status.xls to Col D in the Blank.xls file (and saved as First Name Last Name - New Card Load.xls) to be formatted so that there is no spaces or dashes (or anything separating the number) but just one long 16 digit number and NOT in scientific notation.
Also, need that number in Col T of Applicant Status.xls - when extracted from a emailed spreadsheet (see https://www.experts-exchange.com/questions/28113301/Import-more-data-into-spreadsheet-already-worked-on.html) - to be forced to format into one long 16 digit number as well.
They often email the number separated by dashes or spaces.
Thank you.
Now needing the card number imported from Col T Applicant Status.xls to Col D in the Blank.xls file (and saved as First Name Last Name - New Card Load.xls) to be formatted so that there is no spaces or dashes (or anything separating the number) but just one long 16 digit number and NOT in scientific notation.
Also, need that number in Col T of Applicant Status.xls - when extracted from a emailed spreadsheet (see https://www.experts-exchange.com/questions/28113301/Import-more-data-into-spreadsheet-already-worked-on.html) - to be forced to format into one long 16 digit number as well.
They often email the number separated by dashes or spaces.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ok fine no problem pls go ahead. Appreciate you do not mention my nick in your questions order not offend other Experts. Tks ur understanding.
gowflow
gowflow
ASKER
will do. Having a slight problem now that I just noticed. When I put or delete a value from Col Q, O, R or any column, it is now asking me if I want to send an email.
oops !!! My mistake, here is the fix
1) Open your latest file
2) goto vba and doubleclick on sheet1 and display worksheet_change event
3) Delete all the code that is between Sub Worksheet_Change and the last line End Sub
4) Paste the below code after Sub Worksheet_change
5) SAVE and Exit
6) Try it
Sorry for that
gowflow
1) Open your latest file
2) goto vba and doubleclick on sheet1 and display worksheet_change event
3) Delete all the code that is between Sub Worksheet_Change and the last line End Sub
4) Paste the below code after Sub Worksheet_change
Dim cCell As Range
Dim fName As String
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
End If
End If
End If5) SAVE and Exit
6) Try it
Sorry for that
gowflow
ASKER
ok, that fixed it. Thank you.
Here's the next question:
https://www.experts-exchange.com/questions/28159451/Part-10-to-Import-more-data-into-spreadsheet.html
Here's the next question:
https://www.experts-exchange.com/questions/28159451/Part-10-to-Import-more-data-into-spreadsheet.html
ASKER
Ready for the next one? Trying to get this whole thing automated as you can tell. It's repeated actions for me that you are helping me with a LOT!