I need help with VB code

[ILoveMyCorgi]

I have an Excel file:
# Date Per0 Per1 Per2 Per3 Per4 Per5 Per6
Per7
10001 8/25/2008 T T T T T
T
etc. for approximately 50 rows with different data

The output to display on a new worksheet as follows:
10001 0, 1, 3, 4, 5, 7 [there needs to be a space after the comma]

If I can get help on this I'd be appreciative. Thanks for all your help.

 

[Joel]

Sub formatData()

NewRow = 1
With Sheets("sheet1")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
Num = .Range("A" & RowCount)
Data = ""
For ColCount = 3 To 10
If .Cells(RowCount, ColCount) = "T" Then
If Data = "" Then
Data = ColCount - 3
Else
Data = Data & ", " & (ColCount - 3)
End If
End If
Next ColCount
With Sheets("sheet2")
.Range("A" & NewRow) = Num
.Range("B" & NewRow) = Data
NewRow = NewRow + 1
End With
RowCount = RowCount + 1
Loop
End With

End Sub

 

[Per Jessen]

hI

Try if this is what you need.

Sub AAA()
Set OutputSh = Sheets("sheet2")
Set StartCell = Range("A2")
lastrow = StartCell.End(xlDown).Row
For r = 2 To lastrow
Output = Cells(r, 1).Value & " "
For c = 3 To 10
If Cells(r, c).Value = "T" Then
Output = Output & c - 3 & ", "
End If
Next
Output = Left(Output, Len(Output) - 2)
OutputSh.Range("A1").Offset(off, 0) = Output
off = off + 1
Next
End Sub

Regards
Per

 

[ILoveMyCorgi]

Thanks, Joel. This worked... I am new to VBA and trying to learn as I go...
I truly appreciate this website! Have a great Thanksgiving.

Sub formatData()

NewRow = 1
With Sheets("sheet1")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
Num = .Range("A" & RowCount)
Data = ""
For ColCount = 3 To 10
If .Cells(RowCount, ColCount) = "T" Then
If Data = "" Then
Data = ColCount - 3
Else
Data = Data & ", " & (ColCount - 3)
End If
End If
Next ColCount
With Sheets("sheet2")
.Range("A" & NewRow) = Num
.Range("B" & NewRow) = Data
NewRow = NewRow + 1
End With
RowCount = RowCount + 1
Loop
End With

End Sub

I have an Excel file:
# Date Per0 Per1 Per2 Per3 Per4 Per5 Per6
Per7
10001 8/25/2008 T T T T T
T
etc. for approximately 50 rows with different data

The output to display on a new worksheet as follows:
10001 0, 1, 3, 4, 5, 7 [there needs to be a space after the comma]

If I can get help on this I'd be appreciative. Thanks for all your help.

 

[ILoveMyCorgi]

Thanks, Per. Very helpful. I just replied to Joel also... I appreciate this
feedback so much since I am learning VBA as I go! Having two different ways
of accomplishing this problem helps me learn this more!! Have a great
holiday.

hI

Try if this is what you need.

Sub AAA()
Set OutputSh = Sheets("sheet2")
Set StartCell = Range("A2")
lastrow = StartCell.End(xlDown).Row
For r = 2 To lastrow
Output = Cells(r, 1).Value & " "
For c = 3 To 10
If Cells(r, c).Value = "T" Then
Output = Output & c - 3 & ", "
End If
Next
Output = Left(Output, Len(Output) - 2)
OutputSh.Range("A1").Offset(off, 0) = Output
off = off + 1
Next
End Sub

Regards
Per

I have an Excel file:
# Date Per0 Per1 Per2 Per3 Per4 Per5 Per6
Per7
10001 8/25/2008 T T T T T
T
etc. for approximately 50 rows with different data

The output to display on a new worksheet as follows:
10001 0, 1, 3, 4, 5, 7 [there needs to be a space after the comma]

If I can get help on this I'd be appreciative. Thanks for all your help.