VBA Macro code for =Concat(Transpose) formula

[Browneh89]

Hello,

I am having trouble recording a macro that separates a list of value in one cell separated by a comma.

the formula I use is =CONCAT(TRANSPOSE(ARRAY)&",") I use Shift and down arrow to select the array and F9 to convert that into separate values however the macro recorder seems not to understand these actions.

Can anyone please help me with this, would be much appreciated?

 

[eduzs]

I suppose CONCAT will not return a comma separated list of values.

 

[Browneh89]

I suppose CONCAT will not return a comma separated list of values.

Well This formula does work, you need to press F9 within the Array (B1:B100) and it breaks it down into the list of values but these actions are not recorded within the macro.

 

[Fluff]

What exactly are you trying to do?

 

[Browneh89]

What exactly are you trying to do?

What exactly are you trying to do?

I am attempting to make a Macro which will do the following automatically,

When I press F9 within the Array (A2:A8) I get the result in C4.

The list can vary in length, I used shift and down arrow to select the list and F9 when I attempted to record the macro however the recorder does not detect these actions.

 

[Fluff]

I understand what you are doing the question is why? What do you want to do with the comma separated list?

 

[Browneh89]

I understand what you are doing the question is why? What do you want to do with the comma separated list?

Oh just to copy and paste it into a separate system. the comma allows you to select the values in a group where as otherwise you would need to select them individually which is time consuming.

 

[Fluff]

OK, I don't understand why you need to use F9, as the formula will give you the comma separated list, but will you always be looking at A2 down to the last used row, or could the start point be anywhere?

 

[Browneh89]

OK, I don't understand why you need to use F9, as the formula will give you the comma separated list, but will you always be looking at A2 down to the last used row, or could the start point be anywhere?

Yeah it will always be from A2 and below.

That's strange because without the F9 I don't get the desired result I just get the first value and a comma (Shown in the picture above in C2).

 

[Fluff]

Just realised you profile is showing that you are using 2013, is that correct?
Just that 2013 doesn't have the Concat function.