VBA Formula Assistance

[jstevens]

I'm having a challenge with writing a formula to a cell via code:

Example:
Sheets("Sheet1").Range("A1").Formula = "="Some text here: " & Sheet2!C2"

I'm receiving a compile error: syntax error

I have tried adding additional quote marks to the end of the statement but to no avail.

Thanks for taking a look,
John

[HansV]

It should be

Sheets("Sheet1").Range("A1").Formula = "=Some text here: " & Sheet2!C2

[jstevens]

Hans,

I'm now coming up with another error:

run-time error 424: object required

Regards,
John

[HansV]

Sorry, my mistake. I didn't notice that you hadn't used VBA for the last part.

Sheets("Sheet1").Range("A1").Formula = "=Some text here: " & Worksheets("Sheet2").Range("C2")

[jstevens]

I'm having one of those days...

Thank you,
John

[Reimer]

Me too.
I was going to reply but could not get it to work, so I went to break.
On the way there I remembered -All you have to do is Record a macro and perform the steps. Then look at the macro and tweak.
(Just in case Hans is not avaiable).

[rory]

I would have thought:
Sheets("Sheet1").Range("A1").Formula = "=""Some text here: ""&Sheet2!C2"

[HansV]

You're correct, I wasn't paying attention.