Search for String in Column A, then paste expanded Named range, in offset cell from string

  • Hi I am looking to alter code from Vcoolio the following post:


    Find string match in column then paste adjacent cell

    Here are the steps I am looking to execute:

    1. Look for change in cell value in Sheet5 cell $V$1 (there is a dropdown in this with two options) - at this stage I have attempted make this work via a Worksheet Change
    2. Look for first instance of the String "Test Column" in Column A.
    3. If value of $V$1 is "Big_Column" (without quotes), Select named range "Big_Column" expand the selection by two additional rows - then copy, OR if value of $V$1 is "Small_Column" (without quotes), Select named range "Small_Column" expand the selection by two additional rows - then copy
    4. Paste expanded selection 1 row down and 7 columns to the right of the 'found' string.
    5. Look for next instance of "Test Column" in Column A, then repeat steps 2 and 3, till all found, then end.

    Any help to get this code working would be much appreciated.


    Thanks



    Here is my edited code. I have only attempted to paste one of the ranges in this code but would like to do both. It could be done with two separate macros if this is cleaner, activated by the worksheet change macro.



  • Hello,


    Without your sample file ... just a blind guess ...


    mainly to avoid using Select / Selection ... you could test following:



    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks Carim - I have tested this and it works, so thank you! In trying to add some additional functionality it has stopped working.


    I have attached the sample file.


    I would like to be able to call one of two macros on worksheet change of a dropdown in cell $V$1 (I have named this range "ColumnChange"). I have attempted to do this using a Privatesub but now the original macros don't seem to be working themselves.


    It now is intended to run as follows:


    1. Look for change in cell value in Sheet5, based on this value run the appropriate macro (They are both identical, they just copy and paste a different range)
    2. Paste the copied data as in your macro below.


    Thanks in advance...

  • Hello,


    You could test following

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.Address <> "$V$1" Then Exit Sub
    3. If Target.CountLarge > 1 Then Exit Sub
    4. Select Case Target
    5. Case "Big_Column": PasteFlowsBig
    6. Case "Small_Column": PasteFlowsSmall
    7. End Select
    8. End Sub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Quite honestly ... only looked at designing your instructions ... have not analyzed the underlying logic of your process :(

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • When I have a moment ... will try to understand your logic ... and see what might go wrong ...


    Could you briefly describe in plain English what you are trying to accomplish ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)