Creating a string based on values in varying number of cells

[Bill_Biggs]

I have created a code just now that does work but I know that there is a better way of writing this code. Here is the mission.

I have a spread sheet that returns values to B5 through J5. Somes time only B5 has data, sometime more of them, up to 8. I need a string created that will say what the values are ie, if B5 is alone then the string will say "Jul09" and if the B5 thru D5 have data, the string will come out as "Jul09,Aug09,Sep09".

This is what I wrote:

Dim A, B, C, D, E, F, G, H, I, K As String
Dim J As Integer
A = Range("B5").Value
B = Range("C5").Value
C = Range("D5").Value
D = Range("E5").Value
E = Range("F5").Value
F = Range("G5").Value
G = Range("H5").Value
H = Range("I5").Value
I = Range("J5").Value
Range("B5").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
Loop
J = ActiveCell.Column - 2
If J < 0 Then J = 1
If J = 1 Then K = "" & A & ""
If J = 2 Then K = "" & A & "," & B & ""
If J = 3 Then K = "" & A & "," & B & "," & C & ""
If J = 4 Then K = "" & A & "," & B & "," & C & "," & D & ""
If J = 5 Then K = "" & A & "," & B & "," & C & "," & D & "," & E & ""
If J = 6 Then K = "" & A & "," & B & "," & C & "," & D & "," & E & "," & F & ""
If J = 7 Then K = "" & A & "," & B & "," & C & "," & D & "," & E & "," & F & "," & G & ""
If J = 8 Then K = "" & A & "," & B & "," & C & "," & D & "," & E & "," & F & "," & G & "," & H & ""
If J = 9 Then K = "" & A & "," & B & "," & C & "," & D & "," & E & "," & F & "," & G & "," & H & "," & I & ""

Is there a better way to write this? Actually, I am sure therre is, but I don't know it.

 

[Andrew Poulsom]

You could use Chip Pearson's StringConcat function:

http://www.cpearson.com/Excel/stringconcatenation.aspx

 

[Domski]

Have a look at the Join function.

Dom

 

[xld]

Dim K As String
Dim i As Long
For i = 2 To 10

    K = K & "," & Cells(5, i)
Next i
K = Right$(K, Len(K) - 1)
Do
    K = Replace(K, ",,", ",")
Loop Until InStr(K, ",,") = 0
If Right$(K, 1) = "," Then K = Left$(K, Len(K) - 1)

 

[Bill_Biggs]

Folks,

Thank you all. Andrew, I appreciate your information on Pearson. I was unfamilar with the concat function. Domski, I wa also unaware of the join function. xld, thank you for the immediate aid. Very clever.

Every time I come to this board, I come away smarter thanks to people like you.