Convert a string to cell reference in VBA

iswarya

New Member
Joined
Apr 20, 2010
Messages
18
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?
 
Last edited:

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
try this:
Range("C2").Formula = "='Sheet2'!" & varName

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

iswarya

New Member
Joined
Apr 20, 2010
Messages
18
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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, "'", "")
 
Last edited:

iswarya

New Member
Joined
Apr 20, 2010
Messages
18

ADVERTISEMENT

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

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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

New Member
Joined
Apr 20, 2010
Messages
18
<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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,147
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top