How to Copy a group of cells from an offset relevant to a Search String

EMkayla · Jan 24th 2017

So I have everything but the last step working. I need to search for a phrase in col AO based off a cell on another sheet (Main A2). Then if that cell has that phrase, then I need to copy the cell next to it and the next 13 in that row (so It would be AP-BB cols, row is dynamic & based of the search string. I get the first cell to be selected just fine, but for the life of me I cannot get the macro to highlight all 13 cells (as you can tell, I am a bit of a novice when it comes to VBA, I am self/google/forum taught).

Dim SrchRng As Range, cel As Range
Set SrchRng = Range("AO:AO")
For Each cel In SrchRng
If cel.Value = Worksheets("Main").Range("A2") Then
cel.Offset(0, 1).Select
Range(Cells(0, 0), Cells(0, 13)).Copy

Thanks!

mrmmickle1 · Jan 24th 2017

Re: How to Copy a group of cells from an offset relevant to a Search String

Use ReSize:

Code

Sub Test()




     '=========================================================================================='
     '=========================================================================================='
     'Module Type: Code Module                                                                  '
     'Purpose    : Find X Cell based on criteria then copy 13 cells to right using .Resize      '
     '=========================================================================================='
     'Author     : Matt Mickle                                                                  '
     'LinkedIn   : https://www.linkedin.com/in/matthew-mickle-2101305b                          '
     'Date       : 01.24.2017                                                                   '
     'Forum      : OzGrid                                                                       '
     'URL        : http://www.ozgrid.com/forum/showthread.php?t=202568&p=784267#post784267      '
     '=========================================================================================='




    Dim SrchRng As Range, cel As Range




    Set SrchRng = Range("AO:AO") 'May Consider changing this range it is to large.... Maybe Something Smaller Range("AO2:AO500") ???
    For Each cel In SrchRng
        If cel.Value = Worksheets("Main").Range("A2") Then
            cel.Offset(0, 1).Resize(1, 13).Copy 'From your offset cell get 13 total
                                                'columns (this includes the offset cell)
        End If
    Next cel
    
End Sub

Display More

How to Copy a group of cells from an offset relevant to a Search String

Participate now!

Share