+ Reply to Thread
Results 1 to 16 of 16

If foundrane is nothing then resume

  1. #1
    Registered User
    Join Date
    06-25-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    13

    If foundrane is nothing then resume

    Hey all,

    I'm trying to have a macro move down one cell and resume from the start if it finds nothing, but I'm running into problems....

    Please Login or Register  to view this content.
    Thanks!

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: If foundrane is nothing then resume

    Hello giantfan87,

    What is the problem you are having?

  3. #3
    Registered User
    Join Date
    06-25-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If foundrane is nothing then resume

    It's giving me a runtime error and highlighting the "If foundrange is nothing then" line.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: If foundrane is nothing then resume

    Hello giantfan87,

    Your code snippet does not show if you are assigning the variable FoundRange to the Cells.Find statement. This would cause the error. You code should like similar to this...
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-25-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If foundrane is nothing then resume

    It's still giving me an error, says I need to define an object.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: If foundrane is nothing then resume

    Hello giantfan87,

    You should post your workbook for review.

  7. #7
    Registered User
    Join Date
    06-25-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If foundrane is nothing then resume

    MSG1 = MsgBox("This program may take several moments to complete depending on the number of accounts. Is the first account number selected?", vbYesNo)

    If MSG1 = vbYes Then


    Do




    Selection.Copy
    Sheets("1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Dim foundrange As Range
    Set foundrange = Cells.Find(What:=Range("A1"), after:=ActiveCell).Activate
    Cells.Find(What:=Range("A1"), after:=ActiveCell).Activate
    If foundrange Is Nothing Then
    Sheets("Data").Select
    ActiveCell.Offset(1, 0).Select
    Run (Macro)

    Else

    End If


    Sheets("1").Select
    Cells.Find(What:="Value", after:=ActiveCell).Activate
    ActiveCell.Offset(0, 1).Select
    Selection.Copy
    Sheets("Data").Select
    ActiveCell.Offset(0, 3).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False



    ActiveCell.Offset(1, -20).Select

    Loop Until IsEmpty(ActiveCell)



    Else

    End If


    End Sub

  8. #8
    Registered User
    Join Date
    06-25-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If foundrane is nothing then resume

    Please Login or Register  to view this content.

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: If foundrane is nothing then resume

    Hello giantfan87,

    My fault. I missed the Activate at the end of the Find statement. The code should be like this...
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-25-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If foundrane is nothing then resume

    Ok thanks... getting closer. How do I write the code if I want it to resume at a specific line if it cannot find what is in cell A1?

  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: If foundrane is nothing then resume

    Hello giantfan87,

    How do I write the code if I want it to resume at a specific line if it cannot find what is in cell A1?
    Do you mean a line in the code or a cell on the worksheet?

  12. #12
    Registered User
    Join Date
    06-25-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If foundrane is nothing then resume

    Sorry, a line in the code.

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: If foundrane is nothing then resume

    Hello giantfan87,

    There are 2 ways to do this. You can number each line in your code or use a line label. A line label is a lot easier in my opinion.

    The label must start in column 1 of the code module. If you put code on the same line as the label, add a colon ( to the end of the label.

    Use the GoTo statement to jump to the label. For example, Goto ErrHandler transfers execution to the ErrHandler.

  14. #14
    Registered User
    Join Date
    06-25-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If foundrane is nothing then resume

    How do you label a line?

  15. #15
    Registered User
    Join Date
    06-25-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If foundrane is nothing then resume

    Figured out how to label a line. Problem still persists as follows: I want it to diplay a message saying "works" if something is found. If nothing is found I want it to offset 1 row and go to line "nada". The way it is written it seems to be ignoring the activecell.offset lines.

    Please Login or Register  to view this content.

  16. #16
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: If foundrane is nothing then resume

    Hello giantfan87,

    The ActiveCell should be moving to the next row. Is that not happening?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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