Removing characters
 

I have the following code that takes a collection and send it to a string
then loads the string onto a spreadsheet.

'Add the Bulletin types, reasons and signatures required to their perspective
'strings and load onto the EO_Cover sheet
'First the Bulletin type
For x = 1 To Btype.Count
If Btype.Count < 0 Then
BTstr = BTstr & Btype(x) & " / "
Else
BTstr = BTstr & Btype(x)
End If
Next x
Range("C39") = BTstr

The problem is that when I come to the last item in the collection (list), I
need the " / " to come off. I have tried coding this like a Listbox (see
below)

For x = 1 To Btype.Count -1
If Btype.Count < 0 Then
BTstr = BTstr & Btype(x) & " / "
Else
BTstr = BTstr & Btype(x)
End If
But to no avail. I think it has to do with a listindex, but not sure. Is
there a way (other than searching from the right and removing the character)
that I can remove the ""/" prior to loading the sheet?

Thanks in advance

Removing characters
 

Amenut,

You don't enter your For x loop unless Btype.Count is greater than 0 so I
don't think your first "If" statement will ever be False. Does this
replacement work? It tests whether your For loop is on the last go-round:

If x < Btype.Count Then

hth,

Doug

"asmenut" wrote in message
...
I have the following code that takes a collection and send it to a string
then loads the string onto a spreadsheet.

'Add the Bulletin types, reasons and signatures required to their
perspective
'strings and load onto the EO_Cover sheet
'First the Bulletin type
For x = 1 To Btype.Count
If Btype.Count < 0 Then
BTstr = BTstr & Btype(x) & " / "
Else
BTstr = BTstr & Btype(x)
End If
Next x
Range("C39") = BTstr

The problem is that when I come to the last item in the collection (list),
I
need the " / " to come off. I have tried coding this like a Listbox (see
below)

For x = 1 To Btype.Count -1
If Btype.Count < 0 Then
BTstr = BTstr & Btype(x) & " / "
Else
BTstr = BTstr & Btype(x)
End If
But to no avail. I think it has to do with a listindex, but not sure. Is
there a way (other than searching from the right and removing the
character)
that I can remove the ""/" prior to loading the sheet?

Thanks in advance

Removing characters
 

isn't the last item in the collection Btype.Count
and not Btype.Count -1 - since you begin your counter at 1

either start your counter at 0 like the collection or switch to Btype.Count

your sub is looking at all but the last entry.

"asmenut" wrote:

I have the following code that takes a collection and send it to a string
then loads the string onto a spreadsheet.

'Add the Bulletin types, reasons and signatures required to their perspective
'strings and load onto the EO_Cover sheet
'First the Bulletin type
For x = 1 To Btype.Count
If Btype.Count < 0 Then
BTstr = BTstr & Btype(x) & " / "
Else
BTstr = BTstr & Btype(x)
End If
Next x
Range("C39") = BTstr

The problem is that when I come to the last item in the collection (list), I
need the " / " to come off. I have tried coding this like a Listbox (see
below)

For x = 1 To Btype.Count -1
If Btype.Count < 0 Then
BTstr = BTstr & Btype(x) & " / "
Else
BTstr = BTstr & Btype(x)
End If
But to no avail. I think it has to do with a listindex, but not sure. Is
there a way (other than searching from the right and removing the character)
that I can remove the ""/" prior to loading the sheet?

Thanks in advance