Join elements in an array and output to a single cell

[Xceller]

Hi all,

Thanks in advance for helping. Hope the subject line above is clear enough.

This line of code error out in the code:

Cells(r,"D") = Join(NewText, "/")

Run-time error '5':
Invalid procedure call or argument

Please Login or Register  to view this content.

[MrShorty]

I came across this once before. NewText is Redimmed as a 2D array (even though the first dimension is 1 to 1, it is still a 2D array), but the Join() function can only operate on 1D arrays. Why are you using NewText as a 2D array? If there is no specific reason for NewText to be 2D, the solution is to Redim NewText as a 1D array

Please Login or Register  to view this content.

[Bernie Deitrick]

In addition to the array needing to be 1D instead of 2D, since you are populating the array 1 element at a time, you could skip that and just build the output string:

Please Login or Register  to view this content.

[Xceller]

MrShorty, I suspected it was the problem but wan't sure. Thank you for helping. It work great now.

[Xceller]

Bernie Deitrick - Thank you for helping. A great different approach.

[Bernie Deitrick]

I should also have pointed out a possible issue:

If you have 2 or 3 numbers in a cell, the output could be converted to a date - You might want to format the output cells as text prior to running the code or in code prior to entering the values to prevent that.

[Xceller]

Bernie Deitrick - Great point! I will use a semicolon ";" Thanks