VBA Replace

When I do this,

Email: MikeSmith@AOL.com
will change to

email: mikesmith@aol.com


I want to remove the "email: " text. My code is not working.        

        ElseIf i = 9 Then
                A = Split(Str, "")
                    wks.Cells(R, i) = StrConv(A(0), vbLowerCase) 'e-mail
                Else
                    wks.Cells(R, i) = Trim(Str) 'Fill values
                    wks.Cells(R, i) = Replace(Str, "email: ", "")
                End If
LVL 3
Computer GuyAsked:
Who is Participating?
 
FaustulusConnect With a Mentor Commented:
While looking at your code I saw that you set up the worksheet captions within the main procedure thereby bloating it up and detracting from its purpose which is to write data. The following is a sub that writes the sheet captions.
Private Sub SetCaptions(Ws As Worksheet)

    ' Split always creates a 0-based array
    ' Therefore the string starts with a comma, creating a blank
    ' element, so that Array(1) will be the caption for column 1 = A
    Const Captions As String = ",First name,Last name,Street,City,State,ZIP," & _
                               "First date,Second date,eMail,Date added"
    
    Dim Tmp() As String
    Dim C As Long
    
    Tmp = Split(Captions, ",")
    For C = 1 To UBound(Tmp)
        Ws.Cells(1, C).Value = Tmp(C)
    Next C
End Sub

Open in new window

Call it from your main with a single line of code:-
SetCaptions wks

Open in new window

Since you are passing the worksheet wks to the sub as a parameter the call must come after that worksheet was declared.
0
 
KimputerCommented:
If it's happening while i = 9, it's quite understandable.
Since your code is incomplete, I cannot track what's happening to variable Str

Better have a dummy example file ready, or complete your code (and where the email is in the sheet)
0
 
Computer GuyAuthor Commented:
The email is in the 9th spot of my field:
Copy-of-ImportTextFile.xls
0
 
als315Commented:
Try this sample
Copy-of-ImportTextFile.xls
0
 
FaustulusCommented:
To create a lower case string please use
wks.Cells(R, i).Value = LCase(A(0))

Open in new window

In order to assign Proper case you can use this code,
wks.Cells(R, i).Value = WorksheetFunction.Proper(Tmp(0))

Open in new window

Am I correct in assuming that your input string Str actually is a comma separated string containing all the data, and that the first segment of this string, meaning from the beginning until the first comma, contains the first and last name? If so, do let me know because I started to put in some work into sorting out your code in this sense. Otherwhise, just note that Str is a VB function converting a number into a string. You shouldn't use it as a variable name.
0
All Courses

From novice to tech pro — start learning today.