Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to switch data between rows and columns by using programming?
I have text file like following:
NAME: Mike price: $12 UNIT: 3 NAME: Tom price: $12 UNIT: 3 NAME: SMITH PRICE: $16 UNIT: 6 NAME: Jone price: $22 UNIT: 3 NAME: Yang price: $32 UNIT: 5 NAME: Yubin PRICE: $6 UNIT: 10 How to switch to Excel file like following style? NAME price UNIT Mike $12 3 Tom $12 3 SMITH $16 6 Jone $22 3 Yang $32 5 Yubin $6 10 thank you! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to switch data between rows and columns by using programming?
You could put the text file data in column A of Sheet1
and then run this macro: Sub macro1() Dim iRow As Integer Dim iRow2 As Integer Dim ws As Worksheet Set ws = Sheets("Sheet1") ws.Range("C1") = "NAME" ws.Range("D1") = "PRICE" ws.Range("E1") = "UNIT" iRow = 3 Do Until ws.Cells(iRow, 1) = "" ws.Cells(1 + iRow / 3, 3) = Right(ws.Cells(iRow - 2, 1), Len(ws.Cells(iRow - 2, 1)) - 6) ws.Cells(1 + iRow / 3, 4) = Right(ws.Cells(iRow - 1, 1), Len(ws.Cells(iRow - 1, 1)) - 7) ws.Cells(1 + iRow / 3, 5) = Right(ws.Cells(iRow, 1), Len(ws.Cells(iRow, 1)) - 6) iRow = iRow + 3 Loop End Sub HTH, Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Switch from Columns <- Rows | Excel Discussion (Misc queries) | |||
+++Switch Rows & Columns | Excel Worksheet Functions | |||
How to switch position of columns and rows respectively? | Excel Worksheet Functions | |||
switch rows and columns | New Users to Excel | |||
Switch Rows and Columns | Excel Discussion (Misc queries) |