Pause macro and wait for user to click on a cell to resume macro

  • gerberw
    • #1

    I am a complete newbie, I can record, run and edit a macro. That is pretty well it.

    I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.

    What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.

    Any and all help would be appreciated!

    Cheers.

    • #2

    Re: Pause macro and wait for user to click on a cell to resume macro

    You don't need a loop. add this code to the relevant position in your code

    Any problems post back withn your code

    • #3

    Re: Pause macro and wait for user to click on a cell to resume macro

    Hi thanks for the info, here is what I have tested which doesn't work. Sorry I am a complete newbie when it comes to this.

    • #4

    Re: Pause macro and wait for user to click on a cell to resume macro

    Please take the time to read the Forum Rules and always use code tags when posting code to the Forum

    • #5

    Re: Pause macro and wait for user to click on a cell to resume macro

    That code doesn't use what I posted!

    Why are you bothering pasting to a cell then using that cell's content for the Find, that is completely unnecessary.

    • #6

    Re: Pause macro and wait for user to click on a cell to resume macro

    Hi thanks for your response and the answer to your question. What this macro is doing, is selecting a cell in one worksheet and trying to find a match in another worksheet, then put a Y or N for a match into the adjacent right cell in the original worksheet. I would like to automate this as I have 200 + items to go through. I have included the test macro. Thanks for your patience. Can you recommend a good resource book for Excel/VBA programming.

                                                                                        Sub Test1()'' Test1 Macro'                                                                                                                                                                                                                                                                                                                                                            '    Range("C34").Select    Selection.Copy    Sheets("2012 Proj > 95 Hrs").Select    Range("A155").Select    Cells.Find(What:="1113480036", After:=ActiveCell, LookIn:=xlFormulas, _        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _        MatchCase:=False, SearchFormat:=False).Activate    Sheets("2012 Claimed").Select    Range("D34").Select    Application.CutCopyMode = False    ActiveCell.FormulaR1C1 = "N"    Range("C35").Select    Selection.Copy    Sheets("2012 Proj > 95 Hrs").Select    Range("A155").Select    Cells.Find(What:="1013730076", After:=ActiveCell, LookIn:=xlFormulas, _        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _        MatchCase:=False, SearchFormat:=False).Activate    Sheets("2012 Claimed").Select    Range("C36").SelectEnd Sub
    • #7

    Re: Pause macro and wait for user to click on a cell to resume macro

    If you don't understand the comment in post #4 then please ask for an explanation. Otherwise, it would be appreciated if you edit your last post so it complies with the board rules...

    • #8

    Re: Pause macro and wait for user to click on a cell to resume macro

    I won't continue with this until you respond to post # 4

    • #9

    Re: Pause macro and wait for user to click on a cell to resume macro

    Quote from royUK;698262

    I won't continue with this until you respond to post # 4

    Quote

    I hope I have the format according to your requirements correct.

    Thanks for your response and the answer to your question what this macro is doing, is selecting a cell in one worksheet and trying to find a match in another worksheet, then put a Y or N for a match into the adjacent right cell in the original worksheet. I would like to automate this to the point where it asks for input and then steps through the rest of the macro. I have included the test macro. Thanks for your patience.

    I have gone to your website and have a better idea what the code you originally suggested I use does, thank you. However, I don't understand how to pass the cell contents over to my macro for copying and pasting. I have incorporated your code into my macro, when testing it stops at

    with a run time error.

    • #10

    Re: Pause macro and wait for user to click on a cell to resume macro

    Try this, you will need a loop if you are looking for multiple different values. If you are only looking for multiple instances of one value then you will need a differnt approach

    Try attaching uour workbook for further help and a detailed explanation of what you are doing.

    • #11

    Hi royUK,

    I found your answer in this post (#5) and I'm using it successfully. Just a question:

    Why do you use the On Error Resume Next and On Error Goto 0 around it? I think you don't do this without a reason, but I miss the point.

    Thanks in advance

    • #12

    It's there in case the user chooses Cancel, which will cause an error message. It could be replaced with an error message like this

    • #13

    Thanks RoyUK, it indeed helps! I will use this approacht. Hans