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

[mana]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, c As Range
    
    On Error Resume Next
    Set r = Intersect(Target, Range("A1:A10"))
    On Error Resume Next
  
    If r Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    For Each c In r
        c.Value = Replace(c.Value, ",", ".")
    Next
    
    Application.EnableEvents = True
     
End Sub

[mana]

To prevent event loops


> Application.EnableEvents = False