+ Reply to Thread
Results 1 to 3 of 3

Conditional copying in dynamic range

Hybrid View

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    Rotterdam
    MS-Off Ver
    Excel 2010
    Posts
    16

    Conditional copying in dynamic range

    Hi all,

    In the attached file, the A column consists of quarters. Between some of these there is an error value #Name?, which is replaced with 1 in column B. In column D you find ISIN codes. I want to format this file in such a way that for every 1 in column B a new ISIN is copied in column C. So in C2 until C7 I want to place the value of D2, in C8 till C14 I want to place the value of D3 and in C15 until C69 the value in D4. I've tried to come up with something in VBA, but I'm a bit stuck at the moment. Is there somebody here who can help out?

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Conditional copying in dynamic range

    try
    Option Explicit
    
    Sub ISIN_Copy()
        On Error GoTo ErrorHandler
        
        Dim xlRng As Range
        Dim cellVal
        Dim fAddr$
        Dim i&, lngRowStart&
        
        lngRowStart = 2
        For Each cellVal In Range(Cells(2, 4), Cells(Cells(2, 4).End(xlDown).Row, 4)).Value
            Set xlRng = Columns(2).Find(What:="1", LookIn:=xlValues, lookat:=xlWhole, After:=Cells(lngRowStart, 2))
            If Not xlRng Is Nothing Then
                If fAddr = vbNullString Then
                    fAddr = xlRng.Address
                ElseIf fAddr = xlRng.Address Then
                    Exit For
                End If
                Range(Cells(lngRowStart, 3), Cells(xlRng.Row - 1, 3)).Value = cellVal
                lngRowStart = xlRng.Row
            Else
                Exit For
            End If
        Next cellVal
        Exit Sub
    ErrorHandler:
        MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "Error"
    End Sub
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Registered User
    Join Date
    09-25-2012
    Location
    Rotterdam
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional copying in dynamic range

    Thanks a bunch, this seems to work perfect!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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