Account numbers start with "s"?

Sub Add Data()
dim lastrow as Long
Dim i as Long, loc as String

Columns(1).ClearContents
lastrow = cells(rows.count,2).End(xlup).row

for i = lastrow to 1 step -1
if lcase(Left(cells(i,2),1)) = "s" then
cells(i,1) = Loc
else
loc = cells(i,2)
end if
Next

' optional to delete the location rows
On Error Resume Next
Columns(1).SpecialCells(xlbanks).EntireRow.Delete
On Error goto 0
End Sub


--
Regards,
Tom Ogilvy


"Mascot" wrote in message
...
Hi Tom,

Thanks for getting back to me. I can't seem to get it to work. I guess I
left something out in my explanation. This is a better example of how
column
B looks. Let me know if you can help.

Thanks
Mascot

s1001 (Account)
s1002 (Account)
s1003 (Account)
s1004 (Account)
s1005 (Account)
*J101(Location)
s1001 (Account)
s1002 (Account)
s1003 (Account)
s1005 (Account)
*J102 (Location)
s1001 (Account)
s1002 (Account)
s1003 (Account)
s1005 (Account
*J103 (Location)

etc.


"Tom Ogilvy" wrote:

Assumes account numbers are numeric and locations on alphanumeric as your
example shows.

Sub Add Data()
dim lastrow as Long
Dim i as Long, loc as String

Columns(1).ClearContents
lastrow = cells(rows.count,2).End(xlup).row

for i = lastrow to 1 step -1
if isnumeric(cells(i,2)) then
cells(i,1) = Loc
else
loc = cells(i,2)
end if
Next

' optional to delete the location rows
On Error Resume Next
Columns(1).SpecialCells(xlbanks).EntireRow.Delete
On Error goto 0
End Sub

Test it on a copy of your data.

--
Regards,
Tom Ogilvy


"Mascot" wrote:

Hi,

I have a spreadsheet were I have list of accounts and they are
catergorized
by location in the same column. for example in Column B

1001 (Account)
1002 (Account)
1003 (Account)
1004 (Account)
1005 (Account)
J101(Location)
1001 (Account)
1002 (Account)
1003 (Account)
1005 (Account)
J102 (Location)

so it will list the accounts and then the last item will be the
location.
What I want to do is have the location in column a right next to the
account.
Doea anyone have a macro that can go down the list put the location in
front
of the account?

Thanks
Mascot