On Sat, 4 Aug 2012 08:28:41 -0700 (PDT), noname wrote:



i worked out this worksheet Array formula:

={SUBSTITUTE(CELL("address",INDEX(A1:E1,1,COLUMN( A1:E1))),"$","")}

which gives me correct result i.e. "A1","B1","C1","D1","E1" across multiple cells.

But question is: how do you i convert this into an equivalent single-line code in VBA? as far as i know, the worksheet CELL function does not have any equivalent in VBA.

Are you now changing your specifications?
Intially, you described a quote-delimited, comma separated string. But this formula of yours only returns one address per cell, and only in a range the same vector and size as your original range reference.

I think your description of what you want is not as clear to me as it seems to be to you.

Perhaps you could rethink how you want to express your requirements; and maybe some more information on the overall project might be useful in devising an efficient solution.