Loop - Find and Replace

[ELeGault]

I am trying to make a find and replace that will find ABS which is any time ABS() is used and replace it with a relative formula ...
I tried using the find and replace to replace all...which works but not in tandem with R1C1 style formulas.

Please Login or Register  to view this content.

So my next thought was a loop... as making it the found cell active first and doing a replace or entering the formula will work and have it go until it is past the last row count...
Could not get a loop to work but here is a code that works, and replaces the next cell in order in which they appear but requires hitting play over and over... I have thousands in various columns to replace...

Please Login or Register  to view this content.

Fingers are crossed -

Cheers

[ELeGault]

Currently on all sheets - there are several columns - never goes below 300 Rows! so I tried Do While Rows.Count < 301 but that seemed to not work... currently sitting with the module open and setting a water bottle on a pen that is holding F5 down while it runs through each sheet... lol - has to be a better way

[Crooza]

Total guess here but rather than use cells and step through each one separately can you use this syntax

Worksheets("Sheet1").Columns("A").find

Instead

[protonLeah]

You didn't post the loop code. You should be using something like the code from HELP:

Example
This example finds all cells in the range A1:A500 on
worksheet one that contain the value 2 and changes it to 5.

Please Login or Register  to view this content.

If you have multiple sheets and multiple columns in each sheet, you probably need a couple of For Each... loops to select the sheet then select the column(s) within that sheet the finally the code above. (3 level nesting)

[ELeGault]

Not at home - but will try to wrap it up in something similar - My loop code was garbage so figured I would show the replace method hoping for a suggestion - will play with that code and let you know what I come up with and/or if I hit errors

Cheers

[ELeGault]

So finally got around to this after a weekend of hell dealing with a Transmission...

This is the final (Working) for what I need - though occasionally once finished I get an error

Object Variable or With block not set

Not entirely sure what I am missing... I added an extra set C = Nothing just to clear it but even still happens (Not always and the code finishes so... idk?)

protonLeah, used your quoted with statement - Thanks for that!

Please Login or Register  to view this content.

Will leave it open for another day for error troubleshooting - otherwise - protonLeah and Crooza - Appreciate the assist!

[ELeGault]

So looks like that block error is an issue - Open to suggestions on where I am going wrong -

[romperstomper]

This:

Please Login or Register  to view this content.

is actually incorrect, even though it's what the Help files have always shown. Since both conditions are evaluated for And, if c is Nothing, trying to access c.Address will raise an error.

Given that you are replacing one string containing "ABS" with another string containing "ABS", c shouldn't ever be Nothing inside the loop (though you should be using the .FormulaR1C1 property, not the .Value). How reproducible is the error?

[ELeGault]

Extremely reproducible - It happens with another one I attempted to do which was Findwhat= " $L35 " and ReplaceWithWhat was another formula which would not have had that locked reference in it.

It happens every time the condition is met and the replace takes effect... You can add the code to a blank workbook... add something in any cell then make sure the findwhat is able to locate in that book...

Open for suggestions...

[romperstomper]

If you are replacing all instances with another string that does not contain the original search string, you should only use:

Please Login or Register  to view this content.

If you are replacing with a string that contains the same text, you should only use:

Please Login or Register  to view this content.

[ELeGault]

Works with Loop While Not c Is Nothing, seem to get the same block error with the Loop While C.Address <> firstAddress

So as long as I do not attempt to replace with similar string this is good to go - Thanks RomperStomper!