Find And Replace Macro

  • Thanks in advance for any help.


    I'm struggling with this one and can't seem to find a clear enough answer for me on the forums to accomplish this. I'll say that I have difficulty getting my hands around VBA so please be gentle :)


    Here is what I have accomplished so far with the existing VBA I have written in the sheet. It basically removes blank rows and un-merges any merged cells.



    Once this is accomplished I need to be able to replace the text "MTD Test <insert state> Total:" from each row of column A. Furthermore, I need to fill any blank cells by row in column D with the words "TOTALS:" after the previous 3 macros run.


    I have successfully filled the blank cells using the following code, but I'm not sure how to implement it without setting a static range in the macro.


    Code
    Sub FillBlanks()
        Range("D1:D40").SpecialCells(xlCellTypeBlanks).Value = "TOTALS"
    End Sub


    The sheet should only fill the first 40 rows or so with data, but I need the macros to loop through each row and make the changes then stop -- without any manual selections required.

  • Re: Find And Replace Text Macro


    This little setup below asks the user to enter the text to be replaced and the text to change it to!

    Code
    Sub Macro1()
    Dim IB, IB1 As Variant
    IB = InputBox("Enter Text To Be Replaced", "Find And Replace")
    IB1 = InputBox("Enter Replacement Text", "Find And Replace")
        Cells.Replace What:=IB, Replacement:=IB1, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End Sub

    as for your fill blanks do you only want "total" in column D if there is a value in Column A or does it not matter? when you say "40 rows or so" how many is the so is theer ather data in the rows?


    Regards,
    Simon

  • Re: Find And Replace Text Macro


    Quote from Simon Lloyd

    This little setup below asks the user to enter the text to be replaced and the text to change it to!

    Code
    Sub Macro1()
    Dim IB, IB1 As Variant
    IB = InputBox("Enter Text To Be Replaced", "Find And Replace")
    IB1 = InputBox("Enter Replacement Text", "Find And Replace")
        Cells.Replace What:=IB, Replacement:=IB1, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End Sub

    as for your fill blanks do you only want "total" in column D if there is a value in Column A or does it not matter? when you say "40 rows or so" how many is the so is theer ather data in the rows?


    Regards,
    Simon


    Simon and Dave, thank you for your time. Any row in column A should only have "MTD". Any blank cell in column D after the removal of blank rows and un-merging should have the words "TOTALS:". Another thing I just thought of that would be very helpful to me is taking the un-merged data in any row in Column A and splitting it's contents across it's relative cells to the right as well.


    To summarize, any cell in column A that has text more than just "MTD" should be split apart and fill in the cells to the right in the same row. This may be a more elegant solution?


    Unfortunately, the macro has to be completely automated -- no user intervention with Find/Replace dialogs popping up.


    As far as the 40 rows of data goes this is an estimate. Basically, the end user will paste data in from another sheet and the number of rows may exceed what I have in the example. I only mentioned this because I needed the macro to only loop through rows that contain data.


    The whole point of this for me is to format the data to be extracted to another worksheet using other Excel formulas (HLOOKUP, VLOOKUP, etc.).

  • Re: Find And Replace Macro


    Can you post a sample workbook?, when you say if the cell in Column A has more than MTD spread the rest amongst the other cells, how do you envisage that? i.e MTD1234XYZ should it bet MTD next cell 1 next cell 2 etc? or MTD next cell 1234 next cell XYZ?


    There are too many suppositions in your description, please post your workbook to get a fuller and more useful response.


    I dont think its a "Find And Replace Macro" as your thread title suggests, it could possibly be taken care of with some worksheet functions.


    Regards,
    Simon

  • Re: Find And Replace Macro



    Simon, thanks for your reply. I'll summarize the requirements. Line item 3 has two possible solutions that would work for my requirements.


    I am looking for a Find and Replace option because it will suit my needs, however, I'm looking maybe for a more elegant approach suggestion if it exists.


    • Delete blank rows. SOLVED
    • Un-merge merged cells. SOLVED
    • Any cell in column A that is anything other "MTD" can either be replaced with the word "MTD" or split across adjacent cells into their proper columns.
    • Any cell in column D that is blank to have work "TOTAL:" placed in it. OPTIONAL based on solution used in option 3.


    • End user pastes data into sheet starting at A2. The structure of the data will always be the same, only the contents of the data changes.
    • Formatting of the data with VBA must be completely automatic once they paste the data then click the Format Page button.


    Quote

    i.e MTD1234XYZ should it bet MTD next cell 1 next cell 2 etc? or MTD next cell 1234 next cell XYZ?


    Yes, you have that correct.

  • Re: Find And Replace Macro


    Quote

    Any cell in column A that is anything other "MTD" can either be replaced with the word "MTD" or split across adjacent cells into their proper columns.

    How would you know what their "Proper" columns would be?


    Quote



    Well which one was it? they cant all be correct!

    Regards,
    Simon

  • Re: Find And Replace Macro


    Quote from Simon Lloyd

    How would you know what their "Proper" columns would be?


    Using the example in the sheet I attached this is what should happen with the data. Should the splitting apart of the data be the more functional approach rather that the Find/Replace:


    Once un-merged, A4 "MTD Chris CA Total:" would be split as follows into cells in the same row.


    MTD --> Column A
    Chris --> Column B
    CA --> Column C
    Total: --> Column D

    Quote

    Well which one was it? they cant all be correct!


    MTD next cell 1234 next cell XYZ would be the correct format. Doh! I didn't delete your other example to eliminate the confusion :)

    I know this says find/replace, but I'm also looking that there may be a more functional approach. I've been looking at it from the find/replace stand point since I started this little excercise.

  • Re: Find And Replace Macro


    Quote

    Any cell in column A that is anything other "MTD" can either be replaced with the word "MTD" or split across adjacent cells into their proper columns.
    Any cell in column D that is blank to have work "TOTAL:" placed in it. OPTIONAL based on solution used in option 3.

    EgoProwler, as per the rules, it is 1 question per Thread and the Thread Title must match the question.


    AutoFilter to show anything not = to MTD. Then select your data in Column "A" less the heading, F5 - Special - Visible cells. Now type MTD in the Formula bar and hit Ctrl+Enter.


    Do simliar for Column D using F5 - Special - Blanks.


    To automate AutoFilter and SpecialCells. See these links


    Excel VBA & AutoFilter AutoFilter provides us with a MUCH faster alternative to loops of all kinds


    Excel VBA AutoFilter Criteria How to add the criteria for AutoFilter in VBA.


    Excel VBA AutoFilter Dates Using dates in AutoFilter can be tricky if not using the US date format.


    Create Worksheets for Each Item in an Excel Table of Data I'm often asked how one could create x number of Worksheets where each one houses the data specific to each item in a table. The task at hand is to create 1 Worksheet for each item (named as the item) and have all associated data on it.

Participate now!

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