[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Replace

Posted on 2013-11-14
5
Medium Priority
?
136 Views
Last Modified: 2013-12-21
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
0
Comment
Question by:Computer Guy
5 Comments
 
LVL 37

Expert Comment

by:Kimputer
ID: 39648293
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
 
LVL 3

Author Comment

by:Computer Guy
ID: 39648506
The email is in the 9th spot of my field:
Copy-of-ImportTextFile.xls
0
 
LVL 40

Expert Comment

by:als315
ID: 39648576
Try this sample
Copy-of-ImportTextFile.xls
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39649734
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
 
LVL 14

Accepted Solution

by:
Faustulus earned 2000 total points
ID: 39649790
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

872 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