Using &Chr$(39)& as substitute for ' in VBA

[Paul987]

I'm trying to set the value of cells to exactly the following:

='120!TA_;|={''Sheets(3).Cells(a, 1''}'}

I seem to be having a far more difficult time with the line than I should be.
The ' comments everything behind it out.

Sheets(3).Cells(a, 33) = ????
If someone could throw me a bone here, I'd appreciate it. TIA

[Gary''s Student]

The single quote is O.K. inside a string:

Sub Macro1()
Dim s As String
s = " ' "
Cells(1, 1).Value = s
End Sub


Make your equation as a string variable and then deposit it in a cell
--
Gary''s Student


"Paul987" wrote:

>
> I'm trying to set the value of cells to exactly the following:
>
> ='120!TA_;|={''Sheets(3).Cells(a, 1''}'}
>
> I seem to be having a far more difficult time with the line than I
> should be.
> The ' comments everything behind it out.
>
> Sheets(3).Cells(a, 33) = ????
> If someone could throw me a bone here, I'd appreciate it. TIA
>
>
> --
> Paul987
> ------------------------------------------------------------------------
> Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
> View this thread: http://www.excelforum.com/showthread...hreadid=522246
>
>

[Paul987]

I am having an incredibly difficult time with this. Here is an example:

I want cell (a, 33) to equal exactly ='120.120.120.12!TA_SRV'

This does not work: "='120.120.120.12!TA_SRV'" any ideas?

My actual string is more complicated than this, but I'm hoping to be able to extrapolate any advice receive.

[Paul987]

Anyone else have any adive for me. There seems to be a trick somewhere that I am missing... Thx

[JE McGimpsey]

If you want the literal string

='120.120.120.12!TA_SRV'

in a cell, preformat it as text before assigning the value:

With ActiveCell
.NumberFormat = "@"
.Value = "='120.120.120.12!TA_SRV'"
End With


Alternatively, preface your string with a single quote:

ActiveCell.Value = "'='120.120.120.12!TA_SRV'"

The single quote indicates to XL that the following characters are to be
interpreted as text. You'll see the single quote in the formula bar, but
not in the cell.

OTOH, if you're trying to enter a formula,

='120.120.120.12!TA_SRV'

is not a valid formula. What formula would you successfully enter
manually?



In article <Paul987.24ptby_1142433000.6528@excelforum-nospam.com>,
Paul987 <Paul987.24ptby_1142433000.6528@excelforum-nospam.com> wrote:

> Anyone else have any adive for me. There seems to be a trick somewhere
> that I am missing... Thx