Substitute code partly working

[drewship]

Hello all. I have an interesting problem. I have the following code block in my spreadsheet. The first 'With' section works every time. The second 'With' section only works if I step through the code manually using F8. If I run the the entire macro using the Process button, it seems like the second 'With' block is just skipped alltogether. I tried commenting out the first 'With' block and when the Process button is pressed, the second 'With' section still seems to be skipped. I have attached a copy of the workbook. Can someone help fix this?

Please Login or Register  to view this content.

Thanks,
Andrew

[StephenR]

So small, but so important - you're missing two dots in this line:

Please Login or Register  to view this content.

If you follow Domski's suggestion too, you can dispense with the two loops.

[Domski]

I think you missed some .'s:

Please Login or Register  to view this content.

You could also use Find/Replace instead of the Substitute function.

Dom

[drewship]

Great!!! Thanks to both of you!!!

[rwgrietveld]

The dots in the suggested code(s) works.

Nevertheless I feel I must comment

Please Login or Register  to view this content.

will return a range from A to B

As this range is on the same sheet use

Please Login or Register  to view this content.

[StephenR]

rwgrietveld: is that really necessary?

[rwgrietveld]

Stephen,

No it isn't, IMO best is to use .Range(.Range, .Range) as range is a worksheets property itself

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

I see many constructions and it seems people 'fiddle' till it works.

Maybe nice to start a topic on this.

[StephenR]

There are indeed many ways to skin a cat. I wondered if any particular one was more efficient or rigorous than the others.