string within formula
 

Right now I'm setting the formula on range like this:

oSubTotalSheet.Range("A1:A" & lngLastRow).Formula = "=A1&B1&C1&D1"

I need to insert a delimiter between each cell in the concatenation.
Something like this:

oSubTotalSheet.Range("A1:A" & lngLastRow).Formula =
"=A1&'|'&B1&'|'&C1&'|'&D1"

Is there a way I can do this with a one-liner? I suppose I could just
loop though each row and put the actual value in, but I hoped I go get
this to work.

Thanks,
Woody

string within formula
 

Hi,

oSubTotalSheet.Range("A1:A" & lngLastRow).Formula =
"=A1 & ""|"" & B1 & ""|"" & C1& ""|"" & D1"

Your current formula will give a circular reference.

HTH

"sugargenius" wrote:

Right now I'm setting the formula on range like this:

oSubTotalSheet.Range("A1:A" & lngLastRow).Formula = "=A1&B1&C1&D1"

I need to insert a delimiter between each cell in the concatenation.
Something like this:

oSubTotalSheet.Range("A1:A" & lngLastRow).Formula =
"=A1&'|'&B1&'|'&C1&'|'&D1"

Is there a way I can do this with a one-liner? I suppose I could just
loop though each row and put the actual value in, but I hoped I go get
this to work.

Thanks,
Woody