How to write and concatenate multi elements from an array to a single cell on a worksheet

Hi everyone,

Any help or input would be greatly appreciated. This is the line that I have trouble with: Cells(i, 1) = Missing

Result I am hoping for: 32, 35,49,76
Concatenated with "," between the elements in array

Please Login or Register  to view this content.

Please Login or Register  to view this content.

pjwhitfield - Thank you for your help. Your code works great. I originally put the missing string as an array because it was running really slow, but for some reason, you code doesn't have that problem. Thanks again.

Hi !

Another way with a single dimension array is to use Join function :

Please Login or Register  to view this content.

MarcL - Thanks for your recommendation. I put the Join function in the Array and it still doesn't work.

This is what I did: Cells(i,1) = Join(Missing, ", ")

Where is the word "Missing" in the output you posted?
Just add the word and change this line.

Please Login or Register  to view this content.

Originally Posted by Xceller

I put the Join function in the Array and it still doesn't work.

This is what I did: Cells(i,1) = Join(Missing, ", ")

As I yet wrote, Join function works only with a one dimension array variable …

AB33 - Appreciate your help. Can you explain this line of code Cells(i, 1) = Mid(Temp, 2) and how you delete the first comma? Also, the Mid function requires 3 criteria, but in your Mid function you only have 2.

My code is the same as pjwhitfield's code. Your output shows a comma too.

Temp=,32,35,49,76
Mid(Temp,2)=32,35,49,76

My under standing of the MID function is MID(Text, Start_Num, Num_Char)
So in your Mid function, Temp is the Textstring, the 2 means Num_Char 2? How about the 2nd criterion Start_Num?

Please Login or Register  to view this content.

"My choice" could be any number from 1 to unknown. In your case, we want to return-starting from 2 and number of return is unknown, or entire text.
I hope you got it now.

@AB33 - Thanks again for the explanation. Crystal clear. You are awesome! Have a great Thanksgiving!

You are welcome and have a great one too!