Watch for line wrap:
HowManyTs = Application.CountIf(.Cells(RowCount, "C").Resize(1, 8), "T")
is one line in your code.




Dave Peterson wrote:

Option Explicit
Sub formatData()

Dim NewRow As Long
Dim RowCount As Long
Dim Num As Double
Dim Data As String
Dim ColCount As Long
Dim mySep As String
Dim HowManyTs As Long
Dim TCtr As Long

NewRow = 1
With Sheets("sheet1")
RowCount = 2
Do While .Range("A" & RowCount) < ""
Num = .Range("A" & RowCount).Value
Data = ""
HowManyTs = Application.CountIf(.Cells(RowCount, "C").Resize(1, 8),
"T")
TCtr = 0
For ColCount = 3 To 10
If UCase(.Cells(RowCount, ColCount)) = "T" Then
TCtr = TCtr + 1
If Data = "" Then
Data = ColCount - 3
Else
If TCtr = HowManyTs Then
mySep = ", and, "
Else
mySep = ", "
End If
Data = Data & mySep & 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:

The output has changed a bit... the "and" should only now show up at the next
to the last T if there are more than 2 T's. For instance:
1, 3, and, 5
1, and, 4
or
2

How do I generate that "and" in only one spot in the code you sent me?

"ILoveMyCorgi" wrote:

I need to add the word "and" to the code below so that my output will be, for
instance:
1,[space]and,[space]3 ... etc.
The code I currently have is:
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

How do I modify this code to include the and's?

--

Dave Peterson

--

Dave Peterson