Convert a string to cell reference in VBA

I have a piece of VBA code like this

Cell A1 has the value G4.

varName = Range("A1").Value
Range("C2").Formula = "='Sheet2'!" & varName

What I'm trying to do is write this formula Sheet2!G4 within cell C2. Essentially, I guess I'm trying to achieve an indirect.

But when i run it, C2's formula appears like this:
'Sheet2'!'G4'

It also shows an error. I'm assuming this is because of G4 appearing in quotes when the formula is written in the cell. How can I remove this? How can I change the string varName to a range type? Or is there any other workaround for this?


---------Cross-posted in Mr Excel here.---------------

Please use code tags for all code snippets regardless of how few lines.


Want you have posted works as expected for me.
Can you post workbook to illustrate the problem

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here