+ Reply to Thread
Results 1 to 14 of 14

Replacing Value in Cell Automatically

  1. #1
    Registered User
    Join Date
    10-10-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    75

    Replacing Value in Cell Automatically

    Hi Chaps

    Why Oh, Why wont this work......

    Please Login or Register  to view this content.
    I am trying to get the worksheet to automatically change the word HERZ to 1.HERZ when typed into a cell in column G:G

    Every time I do this it just stares back at me blankly

    Any help would be great!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Replacing Value in Cell Automatically

    The Compare argument is the 6th argument.

    Use this...
    Target.Value = Replace(Target.Value, "HERZ", "1.HERZ", , , vbTextCompare)

    Or this...
    Target.Value = Replace(Target.Value, "HERZ", "1.HERZ", Compare:=vbTextCompare)
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    10-10-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Replacing Value in Cell Automatically

    Thanks, but this still doesn't work...

    I have this in the Worksheet Code so i'm hoping it will do it as people enter the information in the sheet... but its not!

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Replacing Value in Cell Automatically

    Is this in the Worksheet_Change event procedure? Can you show the whole procedure? Are the Application.Events enabled?

  5. #5
    Registered User
    Join Date
    10-10-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Replacing Value in Cell Automatically

    This is what i have so far...

    Please Login or Register  to view this content.

  6. #6
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Replacing Value in Cell Automatically

    updated value has again HERZ..

    try this..
    Please Login or Register  to view this content.
    or this too..

    Please Login or Register  to view this content.
    Last edited by Debraj Roy; 09-12-2013 at 07:55 AM.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Replacing Value in Cell Automatically

    Quote Originally Posted by superdonk View Post
    This is what i have so far...

    Please Login or Register  to view this content.
    Try this...

    Please Login or Register  to view this content.
    Put this in a standard codde module and run it to re-enable events
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    10-10-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Replacing Value in Cell Automatically

    Sorry chaps

    Neither of those worked!

    I'm not sure if I'm doing something wrong!! lol

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Replacing Value in Cell Automatically

    Quote Originally Posted by superdonk View Post
    Sorry chaps

    Neither of those worked!

    I'm not sure if I'm doing something wrong!! lol
    Is the Worksheet_Change code getting triggered?

  10. #10
    Registered User
    Join Date
    10-10-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Replacing Value in Cell Automatically

    It seems like it is!

  11. #11
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Replacing Value in Cell Automatically

    Quote Originally Posted by superdonk View Post
    It seems like it is!
    Can you be more absolute and how do you know?

    The code provided worked for me.

  12. #12
    Registered User
    Join Date
    10-10-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Replacing Value in Cell Automatically

    When I update a value in the spreadsheet the screen flickers as if the file has run a macro, however nothing changes.

    I'm trying to think of some other simple update i can test to see if there's an issue with the sheet itself....any ideas?

    TVM

  13. #13
    Registered User
    Join Date
    10-10-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Replacing Value in Cell Automatically

    I have just tried it in a fresh spreadsheet and it works perfectly!!

    The file I want to put this in does have a few macros and conditional formattings....would this make a difference?

    TVM

  14. #14
    Registered User
    Join Date
    10-10-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Replacing Value in Cell Automatically

    I've got it working!

    thanks for your help chaps!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help Method for automatically replacing text
    By liweiye24 in forum Excel General
    Replies: 1
    Last Post: 07-11-2013, 11:04 PM
  2. Replacing cell value without replacing formula in the cell
    By kingoftheace in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2012, 06:28 AM
  3. Replacing a value with a cell name
    By dextras in forum Excel General
    Replies: 6
    Last Post: 06-12-2009, 10:21 AM
  4. Replies: 5
    Last Post: 02-23-2007, 12:11 PM
  5. Automatically Replacing existing file on save
    By windsurferLA in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2005, 02:06 PM

Tags for this Thread

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