Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I'm trying to generate an address for an alternate worksheet and I'm

running into a syntax problem that I don't understand.

I have the name of the worksheet in one column and I'm trying to write

a macro that will take that worksheet name, add a fixed reference to it.

My code is:

Temp = ActiveCell.Offset(0, -1).Value

ActiveCell.FormulaR1C1 = "='" & Temp & "'" & "!F2"

Value in Temp = "Smith, John J." (which matches the name of one of the

worksheets"

What I'm expecting back is : ='Adams, Michael'!F2

What I get back is : ='Adams, Michael'!'F2'

What am I doing wrong?

The problem lies in the statement Activecell.FormulaR1C1. AS it says the

formula takes in only R1C1 style format formulas.

You can try to use Activecell.FormulaLocal instead. I guess this should

work just as fine.

By the way isn't Temp supposed to return ='Smith, John J.' rather than

='Adams, Michael'

That worked perfectly. Miraculous how 'Adams, Michael' morphed into

'Smith, John J.'??? Too late a night and too little sleep.

Didn't find what you were looking for?
Find more on Constructing Address formulas
Or get search suggestion and latest updates.

Related Topics:

- 2003 - Moving to a formula address
- Get the PC Physical Address or Mac Address
- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- Loop in formula
- Copy down formula with vba
- Array Formula
- Disabling Formulas, Not calculation
- Quotations in Formulas and strings
- Copy Value produced by formula in cell C3 to cell D3
- formula
- Any idea how to write formula?
- Formula with conditional text
- Can Any one help me writing logic for the given formula
- Please Help me in this Formula
- Avoid multiple select constructs
- formatting text which is in text formula
- formula returns to multiple cells, without Array object?
- Need of an Excel Formula
- Formula to match,lookup and then retun alternate value
- Stuck on copying formula from 1 sheet
- Using an array formula as a hyperlink cell reference
- How to detect formula where it is refrering same row
- VBA variable range in a formula
- Formula multiplying ActiveCell with "F11"
- copy down formulas in excel