Convert a string to cell reference in VBA

[iswarya]

I have a piece of VBA code like this

'Cell A1's value is 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?

 

[bobsan42]

try this:
Range("C2").Formula = "='Sheet2'!" & varName

but this assumes that varName is G4 (and is not 'G4')

 

[iswarya]

I don't think the problem is in Sheet2. (I should have mentioned, I used the double quotes as well. My bad. I'm correcting it.) As I said the formula appears with G4 in quotes. how can I get rid of those?

 

[bobsan42]

what exactly do you have in A1?
and what type is varName?

no matter how hard i try i cannot replicate your problem.
try this to see if it works:
Range("C2").Formula = "='Sheet2'!" & Replace(varName, "'", "")

 

[iswarya]

Cell A1 has the value G4.
And varName is a string. Tried declaring it as Range - that didn't work either.

 

[bobsan42]

the question about A1 was more like is there some formula underneath the value G4.
anyway
try this to see if it works:
Range("C2").Formula = "='Sheet2'!" & Replace(varName, "'", "")

 

[iswarya]

<Resolved> Re: Convert a string to cell reference in VBA

Thanks, I found a workaround for this. Used FormulaR1C1 and turned off R1C1 relative references.