I have the format in excel file as below:

Employee Number Location Payment Type Amount ($)
123 City RI $50
City RA $25
City RX $15

124 Brisbane RA $14
Brisbane RU $12


158 Perth RT $56
Perth RA
$11

€¦€¦ and so on €“reach thousand of lines.

If I wish to have this second format €“per example below (in excel) , I do
need to copy and paste the Employee number to each row one by one.Is there
any way (any vb code /macro) that I can use for automation to get the second
report (as example below)?


Format as result of manual copy paste the Employee number as follows:

Employee Number Location Payment Type Amount ($)
123 City RI $50
123 City RA $25
23 City RX $15

124 Brisbane RA $14
124 Brisbane RU $12


158 Perth RT $56
158 Perth RA $11

€¦€¦ and so on €“reach thousand of lines.


Anyone can help and much appreciate any input /suggestion.

PA

see chip pearson's response to "A script to fill in missing text" from
10/31

see if that's what you want. here is is post, but read the op's post, too.


Try the following code:


Dim LastRow As Long
Dim RowNdx As Long
Dim OldVal As String

LastRow = Cells(Rows.Count, "B").End(xlUp).Row
OldVal = Range("A1")
For RowNdx = 1 To LastRow
If Cells(RowNdx, "A").Value = "" Then
Cells(RowNdx, "A").Value = OldVal
Else
OldVal = Cells(RowNdx, "A").Value
End If
Next RowNdx



--


Gary


"PA" wrote in message
...


I have the format in excel file as below:

Employee Number Location Payment Type Amount ($)
123 City RI
$50
City RA
$25
City RX
$15

124 Brisbane RA $14
Brisbane RU
$12


158 Perth RT
$56
Perth RA
$11

.. and so on -reach thousand of lines.

If I wish to have this second format -per example below (in excel) , I do
need to copy and paste the Employee number to each row one by one.Is there
any way (any vb code /macro) that I can use for automation to get the
second
report (as example below)?


Format as result of manual copy paste the Employee number as follows:

Employee Number Location Payment Type Amount ($)
123 City RI
$50
123 City RA
$25
23 City RX $15

124 Brisbane RA $14
124 Brisbane RU $12


158 Perth RT
$56
158 Perth RA
$11

.. and so on -reach thousand of lines.


Anyone can help and much appreciate any input /suggestion.

PA

Yes, many thanks.That's Great.

PA

"PA" wrote:



I have the format in excel file as below:

Employee Number Location Payment Type Amount ($)
123 City RI $50
City RA $25
City RX $15

124 Brisbane RA $14
Brisbane RU $12


158 Perth RT $56
Perth RA
$11

€¦€¦ and so on €“reach thousand of lines.

If I wish to have this second format €“per example below (in excel) , I do
need to copy and paste the Employee number to each row one by one.Is there
any way (any vb code /macro) that I can use for automation to get the second
report (as example below)?


Format as result of manual copy paste the Employee number as follows:

Employee Number Location Payment Type Amount ($)
123 City RI $50
123 City RA $25
23 City RX $15

124 Brisbane RA $14
124 Brisbane RU $12


158 Perth RT $56
158 Perth RA $11

€¦€¦ and so on €“reach thousand of lines.


Anyone can help and much appreciate any input /suggestion.

PA