+ Reply to Thread
Results 1 to 3 of 3

Search for value and exit macro if found

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    Search for value and exit macro if found

    Good day, Gurus.

    I have a macro that copies data from the active workbook to another workbook. If the data is already in the other workbook, however, I want to give a message, "This PO Number is Already in the Tracking Log", and then exit.

    The value I want to search will always be found in cell M2 of the active workbook, and I really only need to search Column M of the other workbook to see if the value is found.

    Here is what I have so far. The area with the extra line breaks is where I need to add the code to search and either give the message and exit or continue to run the macro:
    Sub Move_PO_to_Log()
        Dim POnumber
        Dim wbTemp As Workbook
        Set POnumber = Range("M2")
        Set wbTemp = ActiveWorkbook
        ThisWorkbook.Activate
        
        'The code below will search the whole sheet for the POnumber,
        'and then give an error if it doesn't find it.  I really need
        'it to only search Column M, and give a message and exit if
        'it finds the number, or continue if it doesn't.
        Cells.Find(What:=POnumber, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        
        
        
        'The code belwo will work fine to copy the data needed
        'if the POnumber is not found in the search.
        wbTemp.Activate
        Range("A2:T" & Cells(Rows.Count, "A").End(xlUp).Row).Select
        Selection.Copy
        ThisWorkbook.Activate
        Range("A1048576").End(xlUp).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("U1048576").End(xlUp).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=TODAY()"
        Selection.Copy
        Range(ActiveCell, "U" & Cells(Rows.Count, "A").End(xlUp).Row).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A3").Select
    End Sub
    Thanks in advance for any help you can offer.
    Last edited by hutch@edge.net; 01-08-2014 at 09:38 PM.

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Search for value and exit macro if found

    Try

    Sub Move_PO_to_Log()
        Dim POnumber, c
        Dim wbTemp As Workbook
        Set POnumber = Range("M2")
        Set wbTemp = ActiveWorkbook
        ThisWorkbook.Activate
        
        'The code below will search the whole sheet for the POnumber,
        'and then give an error if it doesn't find it.  I really need
        'it to only search Column M, and give a message and exit if
        'it finds the number, or continue if it doesn't.
        Set c = Columns("M").Find(What:=POnumber.value, After:=POnumber, LookAt:=xlWhole)
        If not c is nothing then
            Msgbox "This PO Number is Already in the Tracking Log"
            Exit Sub
        End If
    多么想要告诉你 我好喜欢你

  3. #3
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    Re: Search for value and exit macro if found

    Perfect, millz. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to Stop & Exit Sub if cell value > 1 was found
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-20-2013, 01:41 AM
  2. Excel Macro Search if not found debug
    By zit1343 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-14-2012, 11:29 AM
  3. [SOLVED] Macro ignores search code when 1 of the 2 search parameters is not found
    By BrodyNoochie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-25-2012, 05:21 PM
  4. How to exit a loop once the value is found in the range
    By Suety in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-28-2010, 01:30 PM
  5. Exit Sub if file/path not found
    By EMoe in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2006, 06:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1