Array as defined list in sheet 'Temp'..

[Sinner]

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

 

[Bob Phillips]

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.

 

[Sinner]

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










- 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.

 

[Bob Phillips]

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

--
__________________________________
HTH

Bob

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










- 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.

 

[Peter T]

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

 

[Bob Phillips]

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 ...

 

[Sinner]

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











- Show quoted text -

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

Thx.

 

[Bob Phillips]

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

--
__________________________________
HTH

Bob

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











- Show quoted text -

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

Thx.

 

[Sinner]

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

--
__________________________________
HTH

Bob





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.

 

[Sinner]

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??