Solved

VBA Replace

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

22 Experts available now in Live!

Get 1:1 Help Now