Solved

VBA Replace

Posted on 2013-11-14
5
119 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 35

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 39

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 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now