+ Reply to Thread
Results 1 to 7 of 7

Find value and if exists copy specific range

  1. #1
    Registered User
    Join Date
    11-20-2013
    Location
    Czech
    MS-Off Ver
    Excel 2010
    Posts
    25

    Find value and if exists copy specific range

    Hello,

    I would like to ask you for help with my VBA code. I need to find value "Spec 012" in row 13 and if this value exists then it should save address of column and copy range 14:100 in that column. If sheet doesn't contain value "Spec 012" then do nothing. I have created this code so far:


    Please Login or Register  to view this content.

    If sheet contains value "Spec. 012" then it works fine but if doesn't then it gets an error. I know that problem is in IF condition, but I don't know how to fix it.
    Sorry for lame question, but I am newbie in VBA.

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Find value and if exists copy specific range

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-20-2013
    Location
    Czech
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Find value and if exists copy specific range

    And if I don't want to exit sub? If r1 is nothing I want to continue in searching for other values ("Spec. 013",...)

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,811

    Re: Find value and if exists copy specific range

    See alternative approach in your duplicate thread ... http://www.excelforum.com/excel-prog...fic-range.html

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    11-20-2013
    Location
    Czech
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Find value and if exists copy specific range

    Thank you. Your alternative approach works great.

    Just for the record I include it into this thread:

    Please Login or Register  to view this content.
    But my first aproach with added "If Not r1 is nothing then..." doesn't work. Maybe it is because if FIND function doesn't find value then it is not "nothing" but error.
    Please Login or Register  to view this content.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,811

    Re: Find value and if exists copy specific range

    Maybe it's because you have IIf rather than If?

    This works for me:

    Please Login or Register  to view this content.

    Regards, TMS

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,811

    Re: Find value and if exists copy specific range

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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] Check if Cell Value exists... If not copy into a different range
    By HJHamm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2015, 02:29 PM
  2. [SOLVED] VBA to Find Specific Text, then Copy range to next open cell under specific text
    By Remotruker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2015, 10:59 AM
  3. Find how many times a cell value exists in another range
    By D.Lovell in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2014, 06:38 PM
  4. 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
  5. Replies: 9
    Last Post: 07-03-2013, 07:39 AM
  6. Copy and paste specific range to date specific range in alternate sheet
    By alanalmarza in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2013, 08:29 AM
  7. Copy adjacent cell if a specific value exists in another cell
    By pjleitner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2010, 04:54 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