Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a Macro?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a Macro?
Can you use a formula?
In cell A1 type: =MID(B1,FIND(" ",B1,1),255) Or if the account # is always 4 numbers/letters In cell A1 type: =MID(B1,5,255) then just drag down the formula as far as needed. Next use copy....pastevalue for column A:A. Also the "trim" function will delete any unwanted spaces -- Mike Q. "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a Macro?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a Macro?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a Macro?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |