Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Replace cell in all sheets? (WinNT4, XL97)

    Okay - I think one of those dreaded loops is required here!

    I need to replace the contents of the same cell, eg T17, in each of quite a few sheets (all in the same workbook, but not including the first three sheets) with the same formula - or preferably the contents of a specific cell, formatting and all.

    I *think* this is going to be something like "for each i in worksheets(4 to worksheets.count), range("T17").contents = worksheets("main").contents, loop until ..."? Or replace with "..." specific text if I can't do the entire cell including formatting.

    Could someone help me with the exact phraseology, please?

    Many thanks!

    PS Is it possible to do a find and replace in the VBE (over more than one sheet, or module, ie the entire project) when the text being put in instead includes more than one line? It would be exTREMEly helpful to know how, if it is!
    Beryl M


  2. #2
    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

    Re: Replace cell in all sheets? (WinNT4, XL97)

    Hi Beryl,
    You could use something like:
    <pre> Dim rngInput As Range, strOutputAddress As String, n As Integer
    strOutputAddress = "D17"
    Set rngInput = Sheets(1).Range("A2")
    rngInput.Copy
    For n = 4 To Sheets.Count
    Sheets(n).Range(strOutputAddress).PasteSpecial
    Next n
    </pre>

    replacing addresses as required.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Replace cell in all sheets? (WinNT4, XL97)

    Rory, that's perfect, thank you so much!

    I don't suppose you know the answer to my PS, do you?!

    ---------------------------------
    PS Is it possible to do a find and replace in the VBE (over more than one sheet, or module, ie the entire project) when the text being put in instead includes more than one line? It would be exTREMEly helpful to know how, if it is!
    ---------------------------------
    Beryl M


  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace cell in all sheets? (WinNT4, XL97)

    No, that is not possible with the Find/Replace dialog. The Find what and the Replace with boxes can only hold one line of text, they won't accept a carriage return, neither entered manually, nor when pasting in text.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Replace cell in all sheets? (WinNT4, XL97)

    <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    I thought you were going to say that.

    Oh well.

    <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Beryl M


Posting Permissions

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