+ Reply to Thread
Results 1 to 2 of 2

Copy Range with Find Function

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-11-2013
    Location
    Austria
    MS-Off Ver
    Excel 2010
    Posts
    136

    Copy Range with Find Function

    Morning All!
    I'm trying to use a find function to set a range to copy. It all runs fine when the values I search for are found, but I would like to manually select a range if the values aren't found.
    So far I've come up with this, but Keep getting Error 91.. Any help?

    Sub CopyRange()
    Dim FindName As Range
    Dim FindEnd As Range
    Dim ManEnd As Range
    Dim CopyRange As Range
    
    Set FindName = Range("A:A").Find(What:="Text").Offset(2)
    Set FindEnd = Range("C:E").Find(What:="Text2").Offset(-2)
        If FindEnd Is Empty Then
        ManEnd = Application.InputBox("Where to?", Type:=8)
    Set CopyRange = Range(FindName.EntireRow, ManEnd.EntireRow)
    Else
    Set CopyRange = Range(FindName.EntireRow, FindEnd.EntireRow)
    End If
    
    CopyRange.Copy
    
    End Sub

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy Range with Find Function

    One thing that may produce an error I notice is this line.

    
    Set FindEnd = Range("C:E").Find(What:="Text2").Offset(-2)
    If "Text2" is found in row 1 or 2 it will produce an error when the code tries to offset.

+ 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. Range.Find function fails to find a match but For loop confirms that match exists
    By 6StringJazzer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-08-2013, 03:35 PM
  2. Macro to Find a Value and Copy a certain Range
    By wxdonar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-21-2013, 11:36 AM
  3. [SOLVED] VBA to Find a value and copy the range the value is in to a different worksheet
    By Benisato in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-29-2013, 02:03 PM
  4. find value in range and copy to another
    By Cicada in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2011, 10:38 AM
  5. select range to copy, find values and copy
    By SarahPintal in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-04-2010, 07:44 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