find and replace values

Posted on 2013-12-29
Medium Priority
Last Modified: 2013-12-30

based on a value in sheet 1 field b1 I would like to loop thru sheet 2 the entire column "a" and find and replace values containing "car" with  the word "transport"
if sheet 1 b1 = 1 then replace values that are months with seaons


if sheet 1 b1 = "1"

the replace "car" with  "transport"


if sheet 1 b1 = "2"

the replace "snacks" with  "food"

if sheet 1 b1 = "3"

the replace "pills" with  "medicine"
Question by:rutgermons

Author Comment

ID: 39745158
I guess substitute may be the better term used for this
LVL 72

Accepted Solution

Qlemo earned 2000 total points
ID: 39745297
In Excel VBA:
Sub SubstA()
  strFind = ""
  strRepl = ""
  Select Case [Sheet1!B1].Value
    Case 1: strFind = "car": strRepl = "transport"
    Case 2: strFind = "snacks": strRepl = "food"
    Case 3: strFind = "pills": strRepl = "medicine"
  End Select
  [Sheet2!A:A].Replace strFind, strRepl, xlWhole
End Sub

Open in new window

Calling that sub (macro) should perform that simple task.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes a serious pitfall that can happen when deleting shapes using VBA.
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

597 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