Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing into different sheets
I have rows that look like this
20050901 2343;3432 20060823 2232;2321 20050401 2322;4434 I want to parse the values into a separate sheet. New sheet should look like: Year Code ------------------------- 2005 2343 2005 3432 2006 2232 2006 2321 2005 2322 2005 4434 Any help would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing into different sheets
dthis works. I assumed data started in column A1. Also assume code with
semicolons was in column B. Sub parsecolumns() Const oldsheet = "Sheet1" Const newsheet = "sheet2" Dim MyYear As String Sheets(oldsheet).Activate LastRow = Cells(Rows.Count, 1).End(xlUp).Row Set DataRange = Range(Cells(1, 1), Cells(LastRow, 1)) Sheets(newsheet).Cells(1, 1) = "Year" Sheets(newsheet).Cells(1, 2) = "Code" RowCount = 2 For Each cell In DataRange MyYear = Left(cell.Value, 4) CodeString = cell.Offset(rowoffset:=0, columnoffset:=1).Value Do While InStr(CodeString, ";") 0 NewCode = Left(CodeString, InStr(CodeString, ";") - 1) CodeString = Mid(CodeString, InStr(CodeString, ";") + 1) Sheets(newsheet).Cells(RowCount, 1) = MyYear Sheets(newsheet).Cells(RowCount, 2) = NewCode RowCount = RowCount + 1 Loop Sheets(newsheet).Cells(RowCount, 1) = MyYear Sheets(newsheet).Cells(RowCount, 2) = CodeString RowCount = RowCount + 1 Next cell End Sub "BerkshireGuy" wrote: I have rows that look like this 20050901 2343;3432 20060823 2232;2321 20050401 2322;4434 I want to parse the values into a separate sheet. New sheet should look like: Year Code ------------------------- 2005 2343 2005 3432 2006 2232 2006 2321 2005 2322 2005 4434 Any help would be appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parsing | Excel Discussion (Misc queries) | |||
Parsing data into seperate sheets | Excel Programming | |||
Instead of Parsing | Excel Discussion (Misc queries) | |||
Parsing a name | Excel Programming | |||
Parsing help | Excel Programming |