ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Add the word "and" in my code (https://www.excelbanter.com/excel-programming/423131-add-word-my-code.html)

ILoveMyCorgi
Add the word "and" in my code
 
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
Add the word "and" in my code
 
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

ILoveMyCorgi
Add the word "and" in my code
 
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

ILoveMyCorgi
Add the word "and" in my code
 
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" 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
Add the word "and" in my code
 
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



ILoveMyCorgi wrote:

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

ILoveMyCorgi
Add the word "and" in my code
 
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" wrote:

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



ILoveMyCorgi wrote:

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" 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
Add the word "and" in my code
 
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.


ILoveMyCorgi wrote:

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" wrote:

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



ILoveMyCorgi wrote:

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

ILoveMyCorgi
Add the word "and" in my code
 
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
Add the word "and" in my code
 
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
Add the word "and" in my code
 
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

ILoveMyCorgi
Add the word "and" in my code
 
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


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

ILoveMyCorgi
Add the word "and" in my code
 
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" wrote:

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

ILoveMyCorgi
Add the word "and" in my code
 
It worked! I just needed to play with it! Thank you so much for all your
help!!
Susan

"Dave Peterson" wrote:

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


All times are GMT +1. The time now is 06:18 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com