mardi 27 juin 2017

VBA - find specific word in column and copy the below's cell on different sheet

I need a bit help with a task below:

I have source data - Example of source data

, which are not aligned to table. I would need to find a text (header - e.g. Account) and copy two whole lines, which are below's the searched cell (Account) and paste them on different Sheet. Then search down and do again until the page with data will be ended and data should be pasted chronologically as it is reached.

The cell with word "Account" will be always in the column A, but number of rows will be different. It should also loop for exact word "Account", because in the column can be cells which contain e.g. "Payer account".

I have this code so far and I've stucked a bit, since it shows me an error msg "Run-time error 438 - object doesnt support this property or method"

Private Sub Search_n_Copy()

Dim LastRow As Long
Dim rng As Range, C As Range

With Worksheets("INPUT_2") ' <-- here should be the Sheet's name
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' last row in column A
    Set rng = .Range("A1:A" & LastRow) ' set the dynamic range to be searched

    ' loop through all cells in column A and copy below's cell to sheet "Output_2"
    For Each C In rng
        If C.Value = "Account" Then
            C.Offset(-1, 0).Copy C.Offset.OUTPUT_2(-7, -1) ' use offset to put value in sheet "Output_2", column E
        End If
    Next C
End With

End Sub

Could you help me with it, please?

Thanks much!

Aucun commentaire:

Enregistrer un commentaire