Consulting

Results 1 to 3 of 3

Thread: Find and Replace

  1. #1
    VBAX Regular
    Joined
    Aug 2016
    Posts
    13
    Location

    Find and Replace

    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

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    763
    Location
    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

  3. #3
    VBAX Expert
    Joined
    Sep 2016
    Posts
    763
    Location
    To prevent event loops


    > Application.EnableEvents = False

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •