Results 1 to 6 of 6

Find and Replace

Threaded View

  1. #1
    Registered User
    Join Date
    08-26-2016
    Location
    england
    MS-Off Ver
    ms office
    Posts
    26

    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
    Last edited by LePig; 09-19-2016 at 07:32 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using find replace to first find two double quotes and replace with a single double quote
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-17-2015, 10:13 AM
  2. [SOLVED] Find and replace where contents a formula so can't be seen by Find and replace.
    By markDuffy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2015, 02:24 PM
  3. Replies: 16
    Last Post: 12-02-2014, 03:38 PM
  4. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  5. find in excel replace in word: find/replace text in text boxes and headers
    By dean.rogers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 12:40 PM
  6. Multiple Find and Replace to replace a list of strings
    By WalterP34 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-11-2011, 07:41 PM
  7. [SOLVED] find and replace - replace data in rows to separated by commas
    By msdker in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2006, 08:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1