+ Reply to Thread
Results 1 to 7 of 7

Find value and if exists copy specific range

Hybrid View

  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:


    Sub Copy_range()
    
    
    Dim r1 As Range
    Dim a1() As String
    Dim Col_1 As String
    
    Set r1 = ActiveSheet.Cells.Find("Spec. 012", LookIn:=xlValues, lookat:=xlWhole)
    
    If (Not IsEmpty(r1.Value)) And (Not IsError(r1.Value)) Then
        a1 = Split(r1.Address, "$")
        Col_1 = a1(1)
    End If
    
    Debug.Print Col_1
    
    ActiveSheet.Range(Col_1 & "14:" & Col_1 & "100").Copy
    
    End Sub

    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

    ...
    Set r1 = ActiveSheet.Cells.Find("Spec. 012", LookIn:=xlValues, lookat:=xlWhole)
    If r1 Is Nothing Then Exit Sub
    ...

  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,630

    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

    If Not r1 Is Nothing Then 
    ' your code
    End If
    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:

    Option Explicit
    
    Sub Copy_range()
    
    Dim awf As WorksheetFunction: Set awf = WorksheetFunction
    Dim aws As Worksheet: Set aws = ActiveSheet
    Dim rRow13 As Range
    Dim lMatch As Long
    
    With aws
        Set rRow13 = .Rows(13)
        On Error Resume Next
        lMatch = 0: lMatch = awf.Match("Spec. 012", rRow13, 0)
        On Error GoTo 0
        If lMatch <> 0 Then
            .Range(.Cells(14, lMatch), .Cells(100, lMatch)).Copy
        End If
    End With
    
    End Sub
    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.
    IIf Not r1 Is Nothing Then
    
        a1 = Split(r1.Address, "$")
        Col_1 = a1(1)
        
    End If

  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,630

    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.

  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,630

    Re: Find value and if exists copy specific range

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

    This works for me:

    Option Explicit
    
    Sub Copy_range_2()
    
    Dim r1 As Range
    Dim a1() As String
    Dim Col_1 As String
    
    Set r1 = ActiveSheet.Cells.Find("Spec. 012", LookIn:=xlValues, lookat:=xlWhole)
    If Not r1 Is Nothing Then
        If (Not IsEmpty(r1.Value)) And (Not IsError(r1.Value)) Then
            a1 = Split(r1.Address, "$")
            Col_1 = a1(1)
        End If
    End If
    Debug.Print Col_1
    
    ActiveSheet.Range(Col_1 & "14:" & Col_1 & "100").Copy
    
    End Sub

    Regards, TMS

+ 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