Hi,

I would appreciate any help if I can use oldwords & newwords array
list as a defined range in another worksheet 'Temp'.
This way it would be flexible to change the values in sheet rather
than in code.
--------------------------------------
Sub ReplaceAll()
OldWords = Array("value1, value2,.........value(n)")
NewWords = Array("value1, value2,.........value(n)")

For i = LBound(OldWords) To UBound(OldWords)
Cells.Replace OldWords(i), NewWords(i)
Next i

End Sub
-------------------------------------

Thx.

Dim OldWords As Range
Dim NewWords As Range

Set OldWords = Range("A2:A14")
Set NewWords = Range("H5:H17")
For i = 1 To OldWords.Cells.Count

NewWords.Cells(i, 1).Copy OldWords.Cells(i, 1)
Next i

etc.

--
__________________________________
HTH

Bob

"Sinner" <(E-Mail Removed)> wrote in message
news:a4b4a481-88b4-4dc2-a9ce-(E-Mail Removed)...
> Hi,
>
> I would appreciate any help if I can use oldwords & newwords array
> list as a defined range in another worksheet 'Temp'.
> This way it would be flexible to change the values in sheet rather
> than in code.
> --------------------------------------
> Sub ReplaceAll()
> OldWords = Array("value1, value2,.........value(n)")
> NewWords = Array("value1, value2,.........value(n)")
>
> For i = LBound(OldWords) To UBound(OldWords)
> Cells.Replace OldWords(i), NewWords(i)
> Next i
>
> End Sub
> -------------------------------------
>
> Thx.

On Sep 17, 3:06*pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
> Dim OldWords As Range
> Dim NewWords As Range
>
> * * Set OldWords = Range("A2:A14")
> * * Set NewWords = Range("H5:H17")
> * * For i = 1 To OldWords.Cells.Count
>
> * * * * NewWords.Cells(i, 1).Copy OldWords.Cells(i, 1)
> * * Next i
>
> etc.
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Sinner" <ims...@gmail.com> wrote in message
>
> news:a4b4a481-88b4-4dc2-a9ce-(E-Mail Removed)...
>
>
>
> > Hi,
>
> > I would appreciate any help if I can use oldwords & newwords array
> > list as a defined range in another worksheet 'Temp'.
> > This way it would be flexible to change the values in sheet rather
> > than in code.
> > --------------------------------------
> > Sub ReplaceAll()
> > OldWords = Array("value1, value2,.........value(n)")
> > NewWords = Array("value1, value2,.........value(n)")
>
> > For i = LBound(OldWords) To UBound(OldWords)
> > * Cells.Replace OldWords(i), NewWords(i)
> > Next i
>
> > End Sub
> > -------------------------------------
>
> > Thx.- Hide quoted text -
>
> - Show quoted text -

Dear can't we define it as a dynamic name in sheet and use that name
in code instead of range??

Thx.

Yes you can, but that doesn't change the principle of my code.

--
__________________________________
HTH

Bob

"Sinner" <(E-Mail Removed)> wrote in message
news:f59dd4dd-f6a0-433c-a990-(E-Mail Removed)...
On Sep 17, 3:06 pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
> Dim OldWords As Range
> Dim NewWords As Range
>
> Set OldWords = Range("A2:A14")
> Set NewWords = Range("H5:H17")
> For i = 1 To OldWords.Cells.Count
>
> NewWords.Cells(i, 1).Copy OldWords.Cells(i, 1)
> Next i
>
> etc.
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Sinner" <ims...@gmail.com> wrote in message
>
> news:a4b4a481-88b4-4dc2-a9ce-(E-Mail Removed)...
>
>
>
> > Hi,
>
> > I would appreciate any help if I can use oldwords & newwords array
> > list as a defined range in another worksheet 'Temp'.
> > This way it would be flexible to change the values in sheet rather
> > than in code.
> > --------------------------------------
> > Sub ReplaceAll()
> > OldWords = Array("value1, value2,.........value(n)")
> > NewWords = Array("value1, value2,.........value(n)")
>
> > For i = LBound(OldWords) To UBound(OldWords)
> > Cells.Replace OldWords(i), NewWords(i)
> > Next i
>
> > End Sub
> > -------------------------------------
>
> > Thx.- Hide quoted text -
>
> - Show quoted text -

Dear can't we define it as a dynamic name in sheet and use that name
in code instead of range??

Thx.

"Sinner" <(E-Mail Removed)> wrote in message

> Dear can't we define it as a dynamic name in sheet and use that
> name in code instead of range??

Does that mean "Oh dear..." or "Dear Bob....", or "That's too expensive..."

Are you looking to replace OldWords(i) with NewWords(i) in all cells in the
sheet, or do something else.

Regards,
Peter T

My mum always use to start a sentence with Dear ... when she was about to
tell me I was being stupid. There was a tone as well, but maybe Sinner meant
the same ... :-)

--
__________________________________
HTH

Bob

"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
>
> "Sinner" <(E-Mail Removed)> wrote in message
>
>> Dear can't we define it as a dynamic name in sheet and use that
>> name in code instead of range??
>
> Does that mean "Oh dear..." or "Dear Bob....", or "That's too
> expensive..."
>
> Are you looking to replace OldWords(i) with NewWords(i) in all cells in
> the sheet, or do something else.
>
> Regards,
> Peter T
>
>
>

On Sep 17, 6:46*pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
> My mum always use to start a sentence with Dear ... when she was about to
> tell me I was being stupid. There was a tone as well, but maybe Sinner meant
> the same ... :-)
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Peter T" <peter_t@discussions> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
>
>
> > "Sinner" <ims...@gmail.com> wrote in message
>
> >> Dear can't we define it as a dynamic name in sheet and use that
> >> name in code instead of range??
>
> > Does that mean "Oh dear..." or "Dear Bob....", or "That's too
> > expensive..."
>
> > Are you looking to replace OldWords(i) with NewWords(i) in all cells in
> > the sheet, or do something else.
>
> > Regards,
> > Peter T- Hide quoted text -
>
> - Show quoted text -

I think i didn't post the reply properly. Did anyone of you got the
reply?? : (
Bob & Peter.

Thx.

Yes, and two of us replied. You haven't reverted on those.

--
__________________________________
HTH

Bob

"Sinner" <(E-Mail Removed)> wrote in message
news:d11bd441-04eb-42d5-ada6-(E-Mail Removed)...
On Sep 17, 6:46 pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
> My mum always use to start a sentence with Dear ... when she was about to
> tell me I was being stupid. There was a tone as well, but maybe Sinner
> meant
> the same ... :-)
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Peter T" <peter_t@discussions> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
>
>
> > "Sinner" <ims...@gmail.com> wrote in message
>
> >> Dear can't we define it as a dynamic name in sheet and use that
> >> name in code instead of range??
>
> > Does that mean "Oh dear..." or "Dear Bob....", or "That's too
> > expensive..."
>
> > Are you looking to replace OldWords(i) with NewWords(i) in all cells in
> > the sheet, or do something else.
>
> > Regards,
> > Peter T- Hide quoted text -
>
> - Show quoted text -

I think i didn't post the reply properly. Did anyone of you got the
reply?? : (
Bob & Peter.

Thx.

On Sep 18, 5:54*pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
> Yes, and two of us replied. You haven't reverted on those.
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Sinner" <ims...@gmail.com> wrote in message
>
> news:d11bd441-04eb-42d5-ada6-(E-Mail Removed)...
> On Sep 17, 6:46 pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
>
>
>
>
>
> > My mum always use to start a sentence with Dear ... when she was about to
> > tell me I was being stupid. There was a tone as well, but maybe Sinner
> > meant
> > the same ... :-)
>
> > --
> > __________________________________
> > HTH
>
> > Bob
>
> > "Peter T" <peter_t@discussions> wrote in message
>
> >news:(E-Mail Removed)...
>
> > > "Sinner" <ims...@gmail.com> wrote in message
>
> > >> Dear can't we define it as a dynamic name in sheet and use that
> > >> name in code instead of range??
>
> > > Does that mean "Oh dear..." or "Dear Bob....", or "That's too
> > > expensive..."
>
> > > Are you looking to replace OldWords(i) with NewWords(i) in all cells in
> > > the sheet, or do something else.
>
> > > Regards,
> > > Peter T- Hide quoted text -
>
> > - Show quoted text -
>
> I think i didn't post the reply properly. Did anyone of you got the
> reply?? : (
> Bob & Peter.
>
> Thx.- Hide quoted text -
>
> - Show quoted text -

O : (

Bob : ) I use dear to everyone.. a habbit i.e. : ) so it was dear
Bod : )

Dear both actually I was looking for a way to replace text in a sheet
'data' base on criteria in a sheet 'temp'. The criteria is in

I have already tried with vlookup but its very time consuming.
Secondly the the ranges of both data & criteria are dynamic. So for
above code, I have to change the range as per requirement. Lets say I
define the data range as a name 'DATA' and criteria as 'CRT'. This way
the macro will be intact & if the ranges changes, I will need to alter
the name range rather than the range values in code.

Lastly the replacement should be irrespective of CaSE, case, CASE
sensitivity.
The previous code replaced words exactly as mentioned in the 'Temp'
sheet which it should not do.

Sample file is at http://www.savefile.com/files/1777260

Thx for your replies.

On Sep 18, 11:14*pm, Sinner <ims...@gmail.com> wrote:
> On Sep 18, 5:54*pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
>
>
>
>
>
> > Yes, and two of us replied. You haven't reverted on those.
>
> > --
> > __________________________________
> > HTH
>
> > Bob
>
> > "Sinner" <ims...@gmail.com> wrote in message
>
> >news:d11bd441-04eb-42d5-ada6-(E-Mail Removed)...
> > On Sep 17, 6:46 pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
>
> > > My mum always use to start a sentence with Dear ... when she was about to
> > > tell me I was being stupid. There was a tone as well, but maybe Sinner
> > > meant
> > > the same ... :-)
>
> > > --
> > > __________________________________
> > > HTH
>
> > > Bob
>
> > > "Peter T" <peter_t@discussions> wrote in message
>
> > >news:(E-Mail Removed)...
>
> > > > "Sinner" <ims...@gmail.com> wrote in message
>
> > > >> Dear can't we define it as a dynamic name in sheet and use that
> > > >> name in code instead of range??
>
> > > > Does that mean "Oh dear..." or "Dear Bob....", or "That's too
> > > > expensive..."
>
> > > > Are you looking to replace OldWords(i) with NewWords(i) in all cells in
> > > > the sheet, or do something else.
>
> > > > Regards,
> > > > Peter T- Hide quoted text -
>
> > > - Show quoted text -
>
> > I think i didn't post the reply properly. Did anyone of you got the
> > reply?? : (
> > Bob & Peter.
>
> > Thx.- Hide quoted text -
>
> > - Show quoted text -
>
> O : (
>
> Bob : ) I use dear to everyone.. a habbit i.e. : ) so it was dear
> Bod : )
>
> Dear both actually I was looking for a way to replace text in a sheet
> 'data' base on criteria in a sheet 'temp'. The criteria is in
>
> I have already tried with vlookup but its very time consuming.
> Secondly the the ranges of both data & criteria are dynamic. So for
> above code, I have to change the range as per requirement. Lets say I
> define the data range as a name 'DATA' and criteria as 'CRT'. This way
> the macro will be intact & if the ranges changes, I will need to alter
> the name range rather than the range values in code.
>
> Lastly the replacement should be irrespective of CaSE, case, CASE
> sensitivity.
> The previous code replaced words exactly as mentioned in the 'Temp'
> sheet which it should not do.
>
> Sample file is athttp://www.savefile.com/files/1777260
>
> Thx for your replies.- Hide quoted text -
>
> - Show quoted text -

Dim OldWords As Range
Dim NewWords As Range

Set OldWords = Range("A2:A14")
Set NewWords = Range("H5:H17")
For i = 1 To OldWords.Cells.Count


NewWords.Cells(i, 1).Copy OldWords.Cells(i, 1)
Next i


I want the Range(Old List), Range(New list) to be a defined name
How do I do that??