PDA

View Full Version : Find and Replace



LePig
09-19-2016, 04:40 AM
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
09-19-2016, 05:11 AM
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
09-19-2016, 06:05 AM
To prevent event loops


> Application.EnableEvents = False