copy paste

Posted by JASON on December 11, 2001 8:27 AM

after i use the find comand it brings me to a cell lets say it took me to cell A10 I want it to automatically select the next three cells which would be B10,C10,D10 AND THEN COPY THEM AND PASTE THEM TO CELLS A1 B1 C1 D1, THEN I USE THE FIND COMAND AGAIN AND IT WOULD SELECT AGAIN AND COPY PASTE AGAIN BUT THIS TIME THE THE A2 B2 C2 D2. AND SO ON IS THIS POSSIABLE. THANKS JASON

Posted by CMorrigu on December 11, 2001 11:18 AM

look in the excel/vba help for searches.... set some variables... and it's just that easy...

Something like this....
ReportRow = 1
Label = "FindMe" ' get first search value
With Worksheets(1).Range(Cells(1, 1), Cells(500, 1))
Set SearchLabel = .Find(Label, LookIn:=xlValues, LookAt:=xlPart) ' search for Label
If Not SearchLabel Is Nothing Then
ValA = cells(SearchLabel.Row,SearchLabel.Column +1).value
ValB = cells(SearchLabel.Row,SearchLabel.Column +2).value
ValC = cells(SearchLabel.Row,SearchLabel.Column +3).value
Cells(ReportRow, 1).Value = ValA
Cells(ReportRow, 2).Value = ValB
Cells(ReportRow, 3).Value = ValC
End If
End With

and loop it...
ShadowSource