Thank you so much. I needed the top version, believe it or not. I truly
appreciate this. Could you recommend a beginner's VBA book that would have
these types of formulas?

"Dave Peterson" wrote:

You sure you want:
1, and, 3, and, 5, and 7

I would think that:
0, and 3, and 5, and 7

would be more natural.

If you mean the second version, I'd use:

Dim ColCount As Long
Dim RowCount As Long
Dim Data As String
Dim mySep As String
mySep = ", and "

With ActiveSheet
RowCount = 5 'for testing
For ColCount = 3 To 10
If UCase(.Cells(RowCount, ColCount)) = "T" Then
If Data = "" Then
Data = ColCount - 3
Else
Data = Data & mySep & ColCount - 3
End If
End If
Next ColCount
End With

If you really meant the top version, then change this:
mySep = ", and "
to
mySep = ", and, "


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