Add the word "and" in my code

I

ILoveMyCorgi

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?
 
D

Dave Peterson

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, "

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?
Click to expand...
 
I

ILoveMyCorgi

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 said:
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, "

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?
Click to expand...
Click to expand...
 
I

ILoveMyCorgi

I just tried to run this and I'm not sure if I'm supposed to incorporate this
into my original code. If so, where? When I replaced your code, it did not
work.
 
D

Dave Peterson

Maybe...

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

mySep = ", and, "

NewRow = 1
With Sheets("sheet1")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
Num = .Range("A" & RowCount).Value
Data = ""
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
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 just tried to run this and I'm not sure if I'm supposed to incorporate this
into my original code. If so, where? When I replaced your code, it did not
work.

ILoveMyCorgi said:
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?
Click to expand...
Click to expand...
 
I

ILoveMyCorgi

Thanks again... this ran perfectly... I know this is simple programming and I
know I can do it... I just need to do more of this via trial and error. I
really appreciate this!

Dave Peterson said:
Maybe...

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

mySep = ", and, "

NewRow = 1
With Sheets("sheet1")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
Num = .Range("A" & RowCount).Value
Data = ""
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
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 just tried to run this and I'm not sure if I'm supposed to incorporate this
into my original code. If so, where? When I replaced your code, it did not
work.

ILoveMyCorgi said:
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?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

That's a good attitude. I bet you'll learn a lot more from your trials (and
errors) than by your successes.
At least that's true for me.

Thanks again... this ran perfectly... I know this is simple programming and I
know I can do it... I just need to do more of this via trial and error. I
really appreciate this!

Dave Peterson said:
Maybe...

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

mySep = ", and, "

NewRow = 1
With Sheets("sheet1")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
Num = .Range("A" & RowCount).Value
Data = ""
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
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 just tried to run this and I'm not sure if I'm supposed to incorporate this
into my original code. If so, where? When I replaced your code, it did not
work.

:

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?
Click to expand...
Click to expand...
Click to expand...
 
I

ILoveMyCorgi

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?
 
D

Dave Peterson

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
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 said:
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?
Click to expand...
Click to expand...
 
D

Dave Peterson

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




Dave said:
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
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
I

ILoveMyCorgi

Thanks! That additional line of code HowManyTs =
Application.CountIf(.Cells(RowCount, "C").Resize(1, 8), "T") does not run...
Not sure how to read it. Sorry.
Susan
 
I

ILoveMyCorgi

It's me again... I hope I'm not being a pest. I played with the additional
line of code and I the output was
1, 2, 3, 4, 5
without the "and," before the 5

What did I do wrong?

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




Dave said:
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
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?

:

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?
Click to expand...
Click to expand...
Click to expand...
 
I

ILoveMyCorgi

It worked! I just needed to play with it! Thank you so much for all your
help!!
Susan

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




Dave said:
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
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?

:

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Joel - Data Consolidate Limitation 0
retrieve data from an .aspx web page 5
Help Change existing Code 6
Joel - Importing multiple text files to 1 spreadsheet, now importing from excel files 7
Why ? What is error 424 ? 2
Matching Rows 2nd try 2
Merging cell using VB for Excel 4
Textbox input goes to 2 sheets in same wbk 0

Top