Find and Replace

[LePig]

Hi,

I am trying to look through some cells and replace "," with ".".

I need this to happen on worksheet change event.

I only want it to be fired though when specific cells have been changed.

Please see code below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Sheets("Sheet1").Range("A1:A10")
           If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

           For Each MyCell In Range("A1:A10")

           MyCell.Value = Replace(MyCell.Value, ",", ".")

           Next MyCell

           Else

           End If

End Sub

I am getting Error Code -2147417848(80010108) Method Range of Object Worksheet Failed

If i put this code on a button click it works fine. It appears to be when i try to restrict the cells. Any help would be much appreciated.

For Each MyCell In Range("A1:A10")

MyCell.Value = Replace(MyCell.Value, ",", ".")

Next MyCell

Kind Regards

[The_Prist]

I believe Worksheet_Change event is not located in Sheet1. What causes an error method Intersect. One of the options would work:

If Not Application.Intersect(KeyCells, Sheets("Sheet1").Range(Target.Address)) Is Nothing Then

or

Set KeyCells = Me.Range("A1:A10")
If Not Application.Intersect(KeyCells, Target) Is Nothing Then

[LePig]

Hi The_Prist,

Thank you for you help, however it does not work. The Worksheet change event is on sheet1.

Any other ideas greatly appreciated.

Kind Regards

[StephenR]

You need to disable events while you change the cells - don't see why you need to change the whole range anyway, though code can be shortened

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range, mycell As Range

Set KeyCells = Sheets("Sheet1").Range("A1:A10")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    Application.EnableEvents = False
    KeyCells.Replace ",", "."
    Application.EnableEvents = True
End If

End Sub

[The_Prist]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Me.Range("A1:A10")
           If Not Application.Intersect(KeyCells, Target) Is Nothing Then
Application.Enableevents = false
           For Each MyCell In Range("A1:A10")
               MyCell.Value = Replace(MyCell.Value, ",", ".")
           Next MyCell
Application.Enableevents = true
           End If
End Sub

[LePig]

Thank you so much for helping me works a treat.