Excel Replace

[Leighton21]

Hi Guys,

I am using code similar to the following

For Each WS In Worksheets
WS.Cells.Replace What:=Search, Replacement:=Replacement, _
LookAt:=xlPart, MatchCase:=False
Next

to try to replace values in a sheet. My question is can you reference the row number of the cell that is currently being replaced i.e.

Say if I was on row 6 and I wanted to replace and column B had the Value B[Row] I would like to replace Row with the actual rownumber.

I can do it with a loop but the replace seems quicker

Cheers

 

[SkipVought]

Leighton,

Yes, REPLACE is quicker...quicker to give you incorrect results.

Use your loop.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

to do what you want, you would need to use Findnext and a loop - bit more efficient than a pure loop through all records but a loop nonetheless...

There is a vey good example of using findnext in the VBA help files

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question