Need to concatenate varying number of cells using Macro

Question

I need to concatenate a column of cells based on a variable in a previous cell. This will continue on until the specified variable changes. For example:

  A B C D  E
1 x     @1 @1+@2+@3
2 x     @2
3 x     @3
4 y     %1 %1+%2+%3
5 y     %2
6 y     %3
etc.

I need the macro to look at A1 and if it's x then begin a concatenated string in E1. Then move to A2, if it's x add D2 to the concatenated value in E1, then move to A3, if it's x add the value in D3 to the concatenated value in E1, etc. Once it hits a new variable in column A (y) the process starts over. Is this at all possible? Thanks very much for your help!!

Answer 1

Here is a formula, paste into E2 and copy down, that will solve your problem. It won't neatly put your answers into E1, E4 etc, but will cascade down the column.

You could do exactly what your after in VBA though.

Formula:

=IF(A2<>A1,D2,E1&D2)

Answer 2

This is some quick-and-dirty code, but it functions:

Dim i As Integer
Dim j As Integer
i = 1
j = 1

Dim initialValue As String

initialValue = Cells(i, 1).Value

Do While Cells(i, 1).Value <> ""
    Cells(j, 5).Value = ""
    Do While Cells(i, 1).Value = initialValue
        Cells(j, 5).Value = Cells(j, 5).Value & Cells(i, 4).Value
        i = i + 1
    Loop

   initialValue = Cells(i, 1).Value
   j = j + 1
Loop

It assumes that the active sheet is the one with your columns. And the column numbers are hard-coded, and you are starting in row 1.

Answer 3

Try this:

Dim row As Integer 
Dim col As Integer 
Dim working_row As Integer 
Dim rowVal As String, myStr As String 

rowVal = ""
row = 1
col = 4
While Cells(row, 1).Value <> ""
    If Cells(row, 1).Value <> rowVal Then
        myStr = ""
        working_row = row
        rowVal = Cells(row, 1).Value
    End If
    myStr = myStr & CStr(Cells(row, col).Value)
    Cells(working_row, col + 1).Value = myStr
    row = row + 1
Wend