Concatenate string with itself in a "while" loop

[lionne]

Hi there

i'd like to ask some help in solving the following problem.

I've got x rows with data in excel. I need to concatenate cells within one row, starting from a fixed cell position. The number of cells to concatenate is variable, not null cells are to concatenate. Here is a piece of code:


Dim iRow, iCol as Integer
Dim undrly_sub_type as String
'
Dim wb As Workbook
Dim lc As Worksheet
'
Set wb = Excel.ActiveWorkbook
Set lc = wb.Sheets("LogCase")
'
iCol = 3
'Cells to concatenate start at the column 34
While Not IsEmpty(lc.Cells(iRow, iCol))
'here's my stupid guess, that does not work
undrly_sub_type = undrly_sub_type & " , " & lc.Cells(iRow, iCol).Value
iCol = iCol + 1
Wend


say, I have value 1 in Column 34, value 10 in Column 35 and value 20 in Column 36. The undrly_sub_type string I expect to get should look like:
1, 10, 20

How can I reach that?
Would be happy to get any hints...

Thx in advance,

[p45cal]

untested:Dim iRow As Long, iCol As Long
Dim undrly_sub_type As String
'
Dim wb As Workbook
Dim lc As Worksheet
'
Set wb = Excel.ActiveWorkbook
Set lc = wb.Sheets("LogCase")
For iRow = 2 To 10 'I don't know which rows you want to process, so adjust.
iCol = 34
undrly_sub_type = lc.Cells(iRow, iCol).Value
iCol = iCol + 1
'Cells to concatenate start at the column 34
While Not IsEmpty(lc.Cells(iRow, iCol))
'here's my stupid guess, that does not work
undrly_sub_type = undrly_sub_type & ", " & lc.Cells(iRow, iCol).Value 'note a space removed.
iCol = iCol + 1
Wend
lc.Cells(iRow, 34).Value = undrly_sub_type
Next iRow

[CatDaddy]

Sub alex()
Dim iRow, iCol, r As Integer
Dim undrly_sub_type As String

ActiveWorkbook.Sheets("LogCase").Activate

iCol = 34
r = Cells(Rows.Count, iCol).End(xlUp).Row
For iRow = 1 To r
If iRow = 1 Then
undrly_sub_type = Cells(iRow, iCol).Text
Else
undrly_sub_type = undrly_sub_type & " , " & Cells(iRow, iCol).Text
End If
Next iRow
Cells(r + 1, iCol).Value = undrly_sub_type
End Sub

Tested

[lionne]

You are awesome, guys! Thanks for quick reply.
Indeed the solution was the IF THEN ELSE statement, so thank you CatDaddy. However, the values to concatenate were not in the column, as you coded, but in one row.
So I ahve modified the script, and here is how it works with me:

'starting column for concatenation
iCol = 34
idx = 0
'
'find out, how many values are there to concatenate
While Not IsEmpty(lc.Cells(iRow, iCol))
idx = idx + 1
iCol = iCol + 1
Wend
'
'set back the starting point
iCol = 34
For idx = 1 To idx
If idx = 1 Then
undrly_sub_type = lc.Cells(iRow, iCol).Text
iCol = iCol + 1
Else
undrly_sub_type = undrly_sub_type & ", " & lc.Cells(iRow, iCol).Text
iCol = iCol + 1
End If
Next idx
'
MsgBox undrly_sub_type


Thank you again!
I really love this forum!

[snb]

if you want to concatenate the values in row 6 for example and put the result into cell A2 you can use:



sub snb()
cells(2,1)=Join(Application.Index(Rows(6).SpecialCells(2).Value, 1, 0), ",")
end sub

[p45cal]

if you want to concatenate the values in row 6 for example and put the result into cell A2 you can use:



sub snb()
cells(2,1)=Join(Application.Index(Rows(6).SpecialCells(2).Value, 1, 0), ",")
end sub

If the result of the specialcells operation is a non-contiguous range, the join only seems to happen for the first area of that range.

[BrianMH]

I have to concatenate several cells together quite often. I have created a UDF that handles multiple area ranges. It is pretty simple and I have put it into my personal macro workbooks so I always have the forumula available.
I know you have a solution already but you may find it useful.


Public Function DatatoCSV(rData As Range) As String
Dim c As Range
Dim strOut As String
For Each c In rData.Cells
If c.Value <> "" Then
If strOut <> "" Then
strOut = strOut & ", "
End If
strOut = strOut & c.Value
End If
Next
DatatoCSV = strOut

End Function

[snb]

@P45cal

I know, but please read the first post in this thread....

@Brian

simpler would be:


Public Function DatatoCSV(rData As Range) As String
For Each cl In rData.specialcells(2)
DatatoCSV = DatatoCSV & ", "
Next
End Function

[p45cal]

@P45cal

I know, but please read the first post in this thread....


Aaah, true enough :)