Solved

VBA Excel replace strings in active cells

Posted on 2008-10-31
5
2,089 Views
Last Modified: 2013-12-26
Hi,

I am writing a VBA macro for Excel where I need to replace a template string with another value.

For example, a cell may contain the following text : "{Username} processed the delivery on {DeliveryDate}."

Any of the active cells in the worksheet may have one or more of the template strings.

The template strings are to be replaced by actual values entered into a userform.

Is there an easy way to do the replacament (eg. "{Username}" with "John") in all the active cells, other than cycling through each cell (ie. using a replace all function would be really good, but I dont know how to do it in VBA).

Any suggestions appreciated

Greg
0
Comment
Question by:gbergsma
  • 3
5 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 25 total points
ID: 22855043
Greg,
Did you try recording a macro using the Edit...Replace menu item? It exposes the method that you can use in VBA. I've generalized it in the snippet below.

Brad


Sub GlobalReplace()

With Selection

    .Replace What:="{Username}", Replacement:="John", LookAt:=xlPart, MatchCase:=False

    .Replace What:="{Delivery Date}", Replacement:="11/25/2008", LookAt:=xlPart, MatchCase:=False

End With

End Sub

Open in new window

0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 25 total points
ID: 22855049
Hello gbergsma,

Try this:

With ActiveSheet.UsedRange
    .Replace What:="{Username}", Replacement:=UserForm1.TextBox1.Value, LookAt:=xlPart, _
        MatchCase:=False
    .Replace What:="{DeliveryDate}", Replacement:=UserForm1.TextBox2.Value, LookAt:=xlPart, _
        MatchCase:=False
End With

Regards,

Patrick
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22855051
Figures Brad would beat me to it :)
0
 

Author Closing Comment

by:gbergsma
ID: 31512239
Though Brad beat you to it, he missed the "UsedRange", so half points each. Thanks guys
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22855134
Glad to help, Greg :)
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

Suggested Solutions

Title # Comments Views Activity
Keyboard shortcuts for Back & Forward QAT arrows in Excel 3 56
Generating a graph via Excel 3 25
Formula 6 46
Vlookup formula error 15 11
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

910 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

20 Experts available now in Live!

Get 1:1 Help Now