I need help with VB code
 

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.

I need help with VB code
 

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


"ILoveMyCorgi" wrote:

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.

I need help with VB code
 

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

On 20 Nov., 01:33, ILoveMyCorgi
wrote:
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.

I need help with VB code
 

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.

"Joel" wrote:

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


"ILoveMyCorgi" wrote:

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.

I need help with VB code
 

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.

"Per Jessen" wrote:

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

On 20 Nov., 01:33, ILoveMyCorgi
wrote:
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.