Replacing data within one Sheet only

[MaximS]

Hi All,

I've came across strange problem which is bugging some of my macros.

Whenever user has used Find/Replace option with Within Workbook settings. All the macros run after will use that setting.

Is there any parameter to limit find/replace to only Active Sheet?


Regards,

MaximS

[georgiboy]

I may have missunderstood but can you not just use...

ActiveSheet.Cells.Replace What:="1", Replacement:="2"

[MaximS]

Unfortunately this method is not working, it's still using Excel settings.

All is about using Find/Replace with mentioned setting out of VBA some time before runing macro.

Excel default setting is finding/replacing within a sheet but once this been changed to Worksheet it's remebered till the time I close the Excel or change it back to default.

Unfortunately there is no parameter in vba for find/replace method.

[georgiboy]

I see what you mean, how annoying. I found that if you use something like this it does not have the same effect...

ActiveSheet.Cells.find("1").Value = 2

[georgiboy]

This is the best i can do to work round this issue. It will only relace in active sheet and it will replace within strings and single length strings.

Sub Replacer()
Dim Find1 As String
Dim Repl As String

Find1 = 1 'As needed (Find)
Repl = 2 'As needed (Replace)

On Error GoTo Ender

Do
If Len(ActiveSheet.Cells.Find(Find1).Value) = 1 Then
ActiveSheet.Cells.Find(Find1).Value = Repl
Else
If Len(ActiveSheet.Cells.Find(Find1).Value) > 1 Then
ActiveSheet.Cells.Find(Find1).Value = _
Left(ActiveSheet.Cells.Find(Find1).Value, InStr(ActiveSheet.Cells.Find(Find1).Value, Find1) - 1) _
& Repl & Right(ActiveSheet.Cells.Find(Find1).Value, Len(ActiveSheet.Cells.Find(Find1).Value) - _
InStr(ActiveSheet.Cells.Find(Find1).Value, Find1))
End If
End If
Loop

Ender:
End Sub
Hope this helps

[MaximS]

I will try to tweek my code with your idea tommorow as i left everything at work.

One of my ideas was to put a message box attached to workbook_open event asking user to check his settings before running the macro.

Main problem I am facing is series of data replacements in about 15.000 rows with each cell containg string of variable lenght.

Sample data is location description in format FXNC/1000/FGSB/ENC_ some location description , WGHN/ZTHS/1000/BBBC some location description, and so on which is gradualy converted to names grouping certain locations.

Later on I am using converted data as a source data for pivot table.

This report is one of the first run in the morning, so it's been run on correct settings.

That problem was hard to find as it occured only couple times over last few months whenever report was run second time during the day and settings were changed before.