asenav
08-11-2016, 05:33 PM
My idea is to reorganize the data on sheet2, into sheet1, instead of doing it manually:
Lets say in Sheet2, I have data displayed in horizontal form, the rows have diferent lengts:
DATA-B
DATA-A1
DATA-A2
DATA-A3
DATA-A4
...
DATA-A31
DATA-A32
X
Y
A
S
A
D
S
S
D
D
D
S
S
D
D
S
D
F
D
...
S
S
A
D
S
E
And I need to relate the rows from the second columnt until their last to the name in the first row:
DATA-A
DATA-B
Y
X
A
X
A
S
D
S
S
S
S
S
D
D
D
D
..
..
E
S
My problem is that I have tried with loop, but not find the way to put this to work right:
Code:
Sub test()
Dim lastRow As Long
Dim pointerx As Integer
Dim pointery As Integer
Dim n As Integer
Dim cella As String
Dim cellb As String
'MsgBox lastRow
lastRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
For pointery = 2 To lastRow
For pointerx = 2 To 32
cella = Trim(CStr(Sheet2.Cells(pointery, pointerx).Value))
ThisWorkbook.Sheets("Sheet1").Cells(pointerx, 1).Value = cella
If cella = "" Then
Exit For
End If
cellb = Trim(CStr(Sheet2.Cells(pointery, 1).Value))
ThisWorkbook.Sheets("Sheet1").Cells(pointery, 2).Value = cellb
Next pointerx
Next pointery
MsgBox ("finished")
End Sub
I am wondering if I will need a Do while, or another type of loop to do this task. I need help to troubleshoot this. I have attached a file.
Lets say in Sheet2, I have data displayed in horizontal form, the rows have diferent lengts:
DATA-B
DATA-A1
DATA-A2
DATA-A3
DATA-A4
...
DATA-A31
DATA-A32
X
Y
A
S
A
D
S
S
D
D
D
S
S
D
D
S
D
F
D
...
S
S
A
D
S
E
And I need to relate the rows from the second columnt until their last to the name in the first row:
DATA-A
DATA-B
Y
X
A
X
A
S
D
S
S
S
S
S
D
D
D
D
..
..
E
S
My problem is that I have tried with loop, but not find the way to put this to work right:
Code:
Sub test()
Dim lastRow As Long
Dim pointerx As Integer
Dim pointery As Integer
Dim n As Integer
Dim cella As String
Dim cellb As String
'MsgBox lastRow
lastRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
For pointery = 2 To lastRow
For pointerx = 2 To 32
cella = Trim(CStr(Sheet2.Cells(pointery, pointerx).Value))
ThisWorkbook.Sheets("Sheet1").Cells(pointerx, 1).Value = cella
If cella = "" Then
Exit For
End If
cellb = Trim(CStr(Sheet2.Cells(pointery, 1).Value))
ThisWorkbook.Sheets("Sheet1").Cells(pointery, 2).Value = cellb
Next pointerx
Next pointery
MsgBox ("finished")
End Sub
I am wondering if I will need a Do while, or another type of loop to do this task. I need help to troubleshoot this. I have attached a file.