Narrowing "selection.Replace" to single sheet

    Im having a hard to figuring out how to narrow the "selection.replace" code in my macro to only search one sheet of my workbook. My thought was prior to this line including Range ("").Select would give the "selection.replace" a defined area to work in but that appears to be untrue.


    FYI: i am working in excel 2003, Old, outdated i know, but until I outgrow potential of this program i cannot justify the cost of the newest edition.


    Here is the macro being used to condense information on multiple sheets to one sheet, then to exclude empty data replace "x" and "0" with blank cells.

    Hello,


    Your macro can be corrected and improved ... :wink:


    Could you kindly post the whole macro ... from Sub to the End Sub ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Select and Activate are seldom needed. Eventually, you should go beyond the macro recorder. e.g. http://www.tushar-mehta.com/excel/vb...rder/index.htm


    See if this gives you an idea or two.

    That was pretty much the whole macro



    Hello,


    You could test following macro :




    Hope this will help


    Edit : Hello Kenneth ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Unfortunately this did not work :( I still end up with "replace" replacing all "x" & "0" throughout the entire workbook instead of just Range: Schedule. i could even settle for being limited to worksheet "Weekly Schedule"


    Hi again,


    As indicated in the code, you do need to make sure ws is defined as your target worksheet ...:wink:

    Code
    ' Set ws as another worksheet ... if need be ...  
 Set ws = ActiveSheet

    So you need to replace ActiveSheet by Sheets("Weekly Schedule") Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Sorry new to VBA which is why im having some issue's still edited as so with activesheet replaced with "sheets("weekly schedule") it still operates throughout the entire workbook.


    Hello,


    Best solution would be to attach your workbook ... for fixing this issue ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Try adding an irrelevant Find operation first - for example

    Code
    .Range("schedule").Find What:="x"

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Not sure exactly what happened, I opened the workbook this morning, copied the page out to a new book to upload here. Ran the macro one more time to confirm everything would continue to work with only one page of the book and it works just the way it did the first time i set it up.
    Here is the code as it sits now as i do not plan on poking it if it works. Thank you Carim!


    If you have previously done a Find operation manually and specified to look in the whole workbook, your Replace operation in code will pick that up. Doing a spurious Find operation in code as I suggested will clear that.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Quote from rory

    If you have previously done a Find operation manually and specified to look in the whole workbook, your Replace operation in code will pick that up. Doing a spurious Find operation in code as I suggested will clear that.


    Just did a test and you are right, while preparing to upload a single sheet i went through to change names, and numbers by using "find and replace" localized to that one page, then running the macro to "test" and it worked only on that page. So as you stated Rory "find and replace" will take the same setting from a previous "find and replace" done manually and use them in the automated macro thus adding in a line of code that does a "find" on that particular sheet will set up the next line to only find/replace that sheet.

    Glad you could fix your problem ...:wink:


    This is what truly matters ...


    Thanks a lot for your Thanks ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login