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