?
Solved

VBA Replace

Posted on 2013-11-14
5
Medium Priority
?
130 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 36

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 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