excel - repeat row data 1

[kawnz]

I am working with a report that dumps data in the following format.
The first line for each patient shows the account number, name, date, and test code.

142L56887	SMITH,MARY	10/18/04	C300055
0	        MC - MEDICARE	0	        C300450
0	        0	        0	        MISC00005
0	        0	        0	        P801200
0	        0	        0          	P801230
142L68885	JOHNSON,DOROTHY	10/21/04	C300535
0	        MC - MEDICARE	0	        MISC00005
142L80306	JOHNSON,DOROTHY	10/25/04	C300535
0	        MC - MEDICARE	0	        MISC00005
142L82215	BROWN,JAMES	10/25/04	C300650
0	        MC - MEDICARE	0	        M600295

I need to have the lines between the first line, and the next patient, populate columns A, B, C with the information in line 1.

i.e.

142L56887	SMITH,MARY	10/18/04	C300055
142L56887	SMITH,MARY	10/18/04	C300450
142L56887	SMITH,MARY	10/18/04	MISC00005
142L56887	SMITH,MARY	10/18/04	P801200
142L56887	SMITH,MARY	10/18/04	P801230
142L68885	JOHNSON,DOROTHY	10/21/04	C300535
142L68885	JOHNSON,DOROTHY	10/21/04	MISC00005
142L80306	JOHNSON,DOROTHY	10/25/04	C300535
142L80306	JOHNSON,DOROTHY	10/25/04	MISC00005
142L82215	BROWN,JAMES	10/25/04	C300650
142L82215	BROWN,JAMES	10/25/04	M600295

A constant factor with all the account numbers, is that they contain the letter L

 

[TonyJollans]

Hi kawnz,

Are you just looking for some code to overwrite cells in columns A, B and C when column A does not contain an "L"? If so ..

[blue]Dim oCell As Range
For Each oCell In Range("A1", Range("A65535").End(xlUp))
    If InStr(oCell, "L") = 0 Then
        oCell.Resize(1, 3) = "=R[-1]C"
    End If
Next[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[kawnz]

Yes, that is exactly what I was looking for. Thanks so much