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

[Xceller]

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.

[pjwhitfield]

Please Login or Register  to view this content.

Unless you need the Missing Array later, then Id remove it, Ive commented out the relevant lines in the code.

[AB33]

Please Login or Register  to view this content.

[Xceller]

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.

[Xceller]

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.

[Marc L]

Hi !

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

Please Login or Register  to view this content.

[Xceller]

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, ", ")

[AB33]

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.

[Marc L]

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 …

[Xceller]

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.

[AB33]

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

[Xceller]

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?

[AB33]

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.

[Xceller]

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

[AB33]

You are welcome and have a great one too!