DIY Excel Marco Beginner needs help with 'replace'

[Greg]

Hi,

I have a query regarding the 'replace' tool. The best way of describing
what I want to do is below (under A is what I have, under B is what I
want):

A B
Hovis Hovis
Hovis White Hovis
Hovis White Standard Hovis
Kingsmill Kingsmill
Kingsmill White Kingsmill
Kingsmill White Standard Kingsmill

This goes on for a very long time (big excel sheet!).

Now, I know that the replace tool available in the 'edit' function will
work when I say find 'kingsmill *' and replace with 'kingsmill', but
what I know Macros can do is to allow me to place a button on the excel
sheet that i can press and it will replace all of the variables (e.g.
hovis white to hovis, and kingsmill white top kingsmill, etc etc). This
will prevent me from having to go through each variable.

Therefore, I would be very grateful if someone could help me with this.
I am finding it difficult as the sheet is going to be updated every
week, so placing specifc ranges in a script (e.g.
Range("A11").Select) will not be appropriate and it doesnt seem to work
if I just put A or A1:A100).

I would be grateful for any suggestions, but bear in mind I am a DIY
Macros beginner who has only been looking at this for two days! If you
are going to write a script, please explain it to me. Thank you, very
very much!

I look forward to hearing from somone, anyone...thanks

Greg

 

[Paul Lautman]

Hi,

I have a query regarding the 'replace' tool. The best way of
describing what I want to do is below (under A is what I have, under
B is what I want):

A B
Hovis Hovis
Hovis White Hovis
Hovis White Standard Hovis
Kingsmill Kingsmill
Kingsmill White Kingsmill
Kingsmill White Standard Kingsmill

This goes on for a very long time (big excel sheet!).

Now, I know that the replace tool available in the 'edit' function
will work when I say find 'kingsmill *' and replace with 'kingsmill',
but what I know Macros can do is to allow me to place a button on the
excel sheet that i can press and it will replace all of the variables
(e.g. hovis white to hovis, and kingsmill white top kingsmill, etc
etc). This will prevent me from having to go through each variable.

Therefore, I would be very grateful if someone could help me with
this. I am finding it difficult as the sheet is going to be updated
every week, so placing specifc ranges in a script (e.g.
Range("A11").Select) will not be appropriate and it doesnt seem to
work if I just put A or A1:A100).

I would be grateful for any suggestions, but bear in mind I am a DIY
Macros beginner who has only been looking at this for two days! If you
are going to write a script, please explain it to me. Thank you, very
very much!

I look forward to hearing from somone, anyone...thanks

Greg

What about an extra column to the right with the formula =LEFT(A1,FIND("
",A1&" ")-1)
Copy this formula down, then copy the column and Paste Special-Values on the
original column.

 

[Guest]

Without macros the formula:

=IF(ISERROR(LEFT(A1,FIND(" ",A1,1))),A1,LEFT(A1,FIND(" ",A1,1)))
will do what you want.

 

[Greg]

That would work, but I would still have to do it for each variable, and
trying that now, it doesnt actually seem to work. Thanks for your help
though, any other ideas would be welcome.

 

[Greg]

That doesn't appear to work. Firstly that makes the cell merge into the
next one and thus misses one cell out. Secondly, it cuts the word down
to the first word, some of the cells i need to have three words and
others two, e.g. hovis (one word) own label (two words) these are the
variable names, but im trying to cut the next bit out. Thats why I
thought macros would be the tool to do it, ratehr than use and excel
formula.

thanks for your input, that may help me in future.

 

[Paul Lautman]

That would work, but I would still have to do it for each variable,
and trying that now, it doesnt actually seem to work. Thanks for your
help though, any other ideas would be welcome.

Providing that you have spaces between the words, that works fine. I tested
it before posting.

I don't understand what you mean by " but I would still have to do it for
each variable".

Finally, I note in the reply to Gary's Student that you now say that
sometimes you do not only wish to keep the first word, which is something no
one could have known, as it wasn't in your original question! No one will be
able to come up with a macro to do what you want, if you don't even tell us
exactlywhat that is.

 

[Guest]

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

It is clear that you have data with several words separated by spaces and
that you wanted only the first word retained. The worksheet formula seems to
work for me, but if you want to try a macro:

Sub first_word()
Dim r As Range
Dim nLastRow, n As Long
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
For n = 1 To nLastRow
v = Cells(n, 1).Value
If IsEmpty(v) Then
Else
j = InStr(1, v, " ")
If j < 2 Then
Else
Cells(n, 1).Value = Left(v, j - 1)
End If
End If
Next
End Sub

This is coded for column A. It determines its own range. It replaces
multi-word text with the first word.

If I have mis-interpreted your requirements, just update the post and we
will continue.

Have a pleasant weekend!

 

[Greg]

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 <[email protected]>
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.

 

[Guest]

No need to apologize. I now see that you have a translation table (two
columns) and you would like to use it to translate supplied data:

starting with:

a happy dog
a sad cat
Kingsmill White
Own Label

it would produce:

a happy dog
a sad cat
Kingsmill
Own Label

So it would go down the list and, for each item in the list, see if
translation is possible. If translation was possible, it would perform it.
Thus in the example, the only translation would be from:

Kingsmill White to Kingsmill

Look for an update tomorrow !!

 

[Guest]

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.