Hi Greg:

Here is more VBA:

Sub change()
Dim s1(10), s2(10) As String

Sheets("translate table").Select
For i = 1 To 10
s1(i) = Cells(i, 1).Value
s2(i) = Cells(i, 2).Value
Next

Sheets("data").Select

For i = 1 To 10
Range("A1").Select
Cells.Replace What:=s1(i), Replacement:=s2(i), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
End Sub

This uses two worksheets, "translate table" and "data".

In the translate table sheet in columns A & B we have:

Hovis Hovis
Hovis White Hovis
Hovis White Standard Hovis
Kingsmill Kingsmill
Kingsmill White Kingsmill
Kingsmill White Standard Kingsmill
Own Label Own Label
Own Label White Own Label
Own Label White Standard Own Label
The Enjoy Collection White The Enjoy Collection

The "data" worksheet can contain any data you have to process. When the
macro runs, it first goes to the translate worksheet to get the table and
then goes to the data worksheet to perform the translation.
--
Gary''s Student


"Greg" wrote:

Sorry. I apologise for any confusion. It is really difficult to
describe what I want on a messaging board.

From: Gary''s Student - view profile
Date: Sat, May 27 2006 4:23 pm
Email: Gary''s Student
Groups: microsoft.public.excel.programming
Not yet ratedRating:
show options


Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


I am not certain what you want the macro to do. From your posting:

A B
Hovis Hovis
Hovis White Hovis
Hovis White Standard Hovis
Kingsmill Kingsmill
Kingsmill White Kingsmill
Kingsmill White Standard Kingsmill
Own Label Own Label
Own Label White Own Label
Own Label White Standard Own Label
The Enjoy Collection White The Enjoy Collection

I've added two more variables here that I have, that I would like to
change. I simply want to make an all encompassing macros that I could
add to a sheet, as an icon. This icon I would then click and it would
change all the names to how I want them. I have been using this code,
but it means replicating it for every single cell that i want:

FormulaR1C1 = _
"Hovis"
Cells.Replace What:="Hovis White", Replacement:="Hovis",
LookAt:=xlWhole _
, SearchOrder:=xlByColumns, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=False
Range("A11").Select

So then I would have to repeat this for every cell and every
differentiation to the full name. Is there not a simple way of using a
range of cell, e.g. (and this example of script doesn't work)

FormulaR1C1 = _
"Hovis"
Cells.Replace What:="Hovis *", Replacement:="Hovis",
LookAt:=xlWhole _
, SearchOrder:=xlByColumns, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=False
Range("A1:A100").Select

Is there no way of making this work? Is there a way to amend the
script.

I'm really sorry, I'm not macros savvy, I have been DIY'ing it for like
4 days and I can't seem to identify a way to do it, but I know it can
work for what I want. The help is no help at all in Excel!!!


I hope your weekend is going well, I apologise for not being able to be
concise. I hope you can help me. thank you for your patience and
efforts so far, it is much appreciated.