Macro to concatenate or merge cells

[Kell2604]

Hi -

I am working on a macro that I need to concatenate multiple cells (I'm using
the =A1&"_"&B1 formula). My problem is that the range of cells I need to
merge is variable based on the data in a previous column. If cell A30 = X
then add cell D30 to a concantenated list that has already been started;
next if A31 = X then add cell D31 to the afore mentioned list, etc. etc. I
can get the macro to evaluate the criteria but can it add a cell (D30) to a
cell which already has an existing concatenated formula? ie. (=L2&" "&L3&"
"&L4&" "&L5 + a new cell)

Hope your not too confused. Thanks in advance for your help!

 

[Paul C]

Yes VBA can. Use a format like this.

Range("B3").Formula = Range("B3").Formula + "&F5"

If the original formula in B3 was C5&D5, this will change the formula to
C5&D5&F5

Just be careful that the added statment will still constitute a valid
formula. Adding "&F5&" for example would give an error

 

[Kell2604]

Awesome Paul - that solves half of my problem. The other part is that the
macro needs to keep going back to the same cell to add the additional data.
For example, I might have 6 total rows that all have X in column A. It is
the values for column D that I want to merge into one cell for all 6 rows.
So lets say my concatenating is happening in cell E2 the macro needs to keep
going back to E2 to add the new cells worth of data. Should I do some sort
of find and paste. Like telling it to look for the empty cell and then
offset up 1 (assuming the cells beneath my formula are empty - because I'm
not pasting anything in them).

 

[Paul C]

A loop like this would work

Sub test()
Range("A1").Select
For Z = 1 To 10 '(set your start and end row as needed)
If Cells(Z, 1) = "X" Then
Range("E2").FormulaR1C1 = Range("E2").FormulaR1C1 & "&R" & CStr(Z) &"C4"
End If
Next Z
Range("E2").FormulaR1C1 = "=" & Right(Range("E2").FormulaR1C1,
Len(Range("E2").FormulaR1C1) - 1) 'This needs to be one line
'This removes the initial & from the formula and adds the =
End Sub