Can someone please look at this and tell me why I'm getting an extra blank string? I *think* it may have something to do with my first For Loop (For I = 1 To N), but not sure. I've gone back and forth with this for the past hour and I'm getting nowhere. It's causing havoc on a working VBA script that takes a string and inserts commas and an 'and' in the proper spot.
Code:
Private Sub btnPush_Click()
Dim strText As String, I As Integer, N As Integer
Dim Activities As Variant
strText = " "
N = 6
For I = 1 To N
'If Me("cb1") = True Then strText = strText & Me("Action1")
'Next
If Me("cb" & I) = True Then strText = strText & Me("Action" & I) & ";"
Next
MsgBox strText
Dim myActivities() As String
'ADDING MY COMMAS/AND CODE
'Split the string into a dynamic array
myActivities() = Split(strText, ";")
Activities = Join(myActivities, ",")
For z = LBound(myActivities()) To UBound(myActivities())
MsgBox myActivities(z)
Next
MsgBox LBound(myActivities())
MsgBox UBound(myActivities())
Dim result As String
For x = 0 To UBound(myActivities())
If UBound(myActivities()) = 0 Then
result = myActivities(0) & "."
ElseIf UBound(myActivities()) = 1 Then
result = myActivities(0) & " and " & myActivities(1) & "."
Else
If x = UBound(myActivities()) Then
result = result & myActivities(x) & "."
ElseIf x = (UBound(myActivities()) - 1) Then
result = result & myActivities(x) & ", and "
Else
result = result & myActivities(x) & ", "
End If
End If
Next
MsgBox result
Me.Sentence.Value = Name & " " & result
End Sub
Thanks!