Results 1 to 11 of 11

Thread: Edit - Replace

  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good afternoon

    I have a worksheet in which 15,232 cells have Transport! and then a cell reference for example Transport!D6. I thought that I could use replace to change Transport! to =Transport! but I just get a dialog box saying that there is a forumala error.

    There is a way that I can change 372 cells manually and then drag them across to make the changes but I am intrigued to know why it will not replace?
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Do you have a sheet named Transport in your workbook? It should work otherwise - at least it did for me.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the quick response, yes I have a sheet named Transport and that is where all of the cell references are supposed to be pointing once the = sign is added.

    Back off to try again

    Ta
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Are you sure there isn't a leading or trailing space in your sheet name?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have tried again but this is what I get?
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Are you sure there isn't a leading or trailing space in your sheet name?
    Hi Rory

    Thanks for the input, the reference did work initially in my trial sheet with only 372 references to cells in the Transport sheet, but now that I have had to make it work for the 15k cells it does not if I click into any cell and manually add an = before Transport! it works, it just does not seem to want to know when it comes to bulk replacing.

    I think I will just bite the bullet and manually change the 372 and drag them over

    Thanks
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Does it work if you select a small subset of cells and then do the Find/Replace on just those cells?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm not sure it is a bulk issue, I just did a replace with 196,299 cells with out a problem.

    I'm guessing you have a cell with the =Transport! in it already and when you are replacing the Transport! with =Transport!, you end up with a cell with ==Transport!

    Do a replace in with =Transport! to Transport! then try it again.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    =Transport! is not a valid cell reference, it fails a cell address.

    I'd expect to see
    =Transport!A1 or similar.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I wasn't giving a complete cell content description. I was pointing out that there may be a cell that already contains the =.

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mbarron View Post
    I wasn't giving a complete cell content description. I was pointing out that may be a cell that already contains the =.
    Morning

    Sorry for the Tawdry response but my broadband went down at 16:00 and has just come up now.

    Spot on, 1 stupid = sign was all that was doing it.

    Thanks to all for your input
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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