vba code to sort data?

[eadie]

Hi All,

I am an absolute newbie to excel and vba. All I want to do is copy
data which is in the following format :

A B
identifier1: Result1
identifier2: Result2
identifier3: Result3
identifier4: Result4
identifier5: Result5



identifier1: Result6
identifier2: Result7
identifier3: Result8
identifier4: Result9
identifier5: Result10

........
.........

into a new worksheet sorted to look like the following;

A B C D E
identifier1 identifier2 identifier3 identifier4
identifier5
Result1 Result2 Result3 Result4 Result5
Result6 Result7 Result8 Result9
Result10

and so on.....

i think whats causing me grief is the space of about 5 rows seperating
each 'sets' of identifiers and results.

currently i have something similar to the following;

Sub selectData()
Dim field As String
If ActiveCell.Text = "" Then
ActiveCell.Offset(0, -1).Activate
Else: ActiveCell.Offset(1, 0).Activate
End If
field = ActiveCell.Value
Worksheets("Output").Activate
End sub

ive concentrated on trying to check whether if the field is blank or
not...Im not too sure where to go next, I keep getting lost!

any ideas? much appreciated...

eadie.

any ideas?

 

[shockley]

eadie,

Here's some code that will work. Name the sheet with the original data, "1"
and the sheet with the converted data, "2".
Put your "identifier1", "identifier2",... headers in the first five cells of
Row1 on sheet "2".

HTH, Shockley


Sub Tester()
With ThisWorkbook.Sheets("1").Columns(1)
LastRow = .Cells(.Rows.Count).End(xlUp).Row
For i = 1 To LastRow
sID = .Cells(i)
sResult = .Cells(i).Offset(0, 1)
If sID <> Empty Then _
EnterData sID, sResult
Next i
End With
End Sub
Sub EnterData(sID, sResult)
With ThisWorkbook.Sheets("2")
iColumn = Val(Left(Right(sID, 2), 1))
LastRow = .Cells(.Cells.Rows.Count, iColumn).End(xlUp).Row
.Cells(LastRow + 1, iColumn) = sResult
End With
End Sub

'Or, a more general solution for the EnterData sub:

Sub EnterData2(sID, sResult)
With ThisWorkbook.Sheets("2")
sID1 = Left(sID, Len(sID) - 1)
iColumn = .Rows(1).Find(sID1).Column
LastRow = .Cells(.Cells.Rows.Count, iColumn).End(xlUp).Row
.Cells(LastRow + 1, iColumn) = sResult
End With
End Sub

 

[eadie]

Hi Shockley,

thanks for your help, although i cant seem to get it working
the following line is throwing up an error;

LastRow = .Cells(.Cells.Rows.Count, iColumn).End(xlUp).Row

any ideas?

 

[shockley]

eadie,

What's the error you're getting? My guess: "subscript out of range"? If so,
you need to make sure your worksheets are named exactly as I have them in
the macro.

With ThisWorkbook.Sheets("1").Columns(1)

is not the same as

With ThisWorkbook.Sheets(1).Columns(1)

The sheet tabs on the two worksheets should show "1" and "2" without the
quotes.

Regards,
Shockley