Concatenating a "=" to a text string.

[___AW]

I am running a program to reformat some text from one character per line to a string of text. It works fine until it hits some text that has the value of "=". Then I get a "Run-time error '1004': Application-defined or object-defined error". Is there a workaround for this? The code that does the concatenate is as follows:

Cells(RowRefDest, 1).Value = Cells(RowRefDest, 1).Value & Cells(RowRefSrc, 1).Value

Thanks.

 

[Scott Huish]

That's because it will try and interpret that as a formula, try this:

Cells(RowRefDest, 1).Value = "'=" & Cells(RowRefDest, 1).Value & Cells(RowRefSrc, 1).Value

 

[Rick Rothstein]

I am running a program to reformat some text from one character per line to a string of text. It works fine until it hits some text that has the value of "=". Then I get a "Run-time error '1004': Application-defined or object-defined error". Is there a workaround for this? The code that does the concatenate is as follows:

Cells(RowRefDest, 1).Value = Cells(RowRefDest, 1).Value & Cells(RowRefSrc, 1).Value

Do I understand you correctly that you have data that looks something like this...

Excel 2010

	A
1	M
2	y
3
4	D
5	a
6	t
7	a

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

and you want it to look like this...

Excel 2010
	A	B
1	M	My Data
2	y
3
4	D
5	a
6	t
7	a
Sheet2

If so, you can do it with a single line of code (albeit a rather long one)...

Range("[B][COLOR="#0000FF"]B[/COLOR][/B]1").Value = Replace(Replace(Join(Application.Transpose(Range("[B][COLOR="#FF0000"]A[/COLOR][/B]1", Cells( _
                    Rows.Count, "[B][COLOR="#FF0000"]A[/COLOR][/B]").End(xlUp)).Value), ","), ",,", " "), ",", "")

The two red A's are the source column's letter designation and the blue B is the destination column's letter designation.

 

[mikerickson]

Try

Cells(RowRefDest, 1).Value = "'" & Cells(RowRefDest, 1).Value & Cells(RowRefSrc, 1).Value

 

[___AW]

Thanks to all of you. Sorry for the slow feed back, but I just finally got back on this. Actually none of those worked. What did end up working was setting the entire column to Text format before processing it. The source cell value that I was trying to concatenate was a "=", so I just had to format it as a text field before concatenating it.

Rick, thanks for your transpose formula. That is what I was doing, I only had to adjust the formula ranges to match exactly what I was trying to do. That made the macro run much quicker!