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?
- How to add several conditions for formula "SUMIF"
- combining formula
- VBA code to add values in two cells which have formulas
- VBA or array formula?
- Paste Formula help
- R1C1 Formula
- Three Leveled Formula
- Formula using range name in VBA code
- searching within formulas
- Populate Formula(Function) in rows
- Getting a formula to copy from inside VBA
- VBA Formula prob
- formula help but not vba please
- Loop in formula
- Copy down formula with vba
- Array Formula
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- Stuck on copying formula from 1 sheet to another
- Named formula/Windows dialog boxes
- cells containing formula that refer to user-defined VBA function
- sum formula
- Manually creating a row outline with no formula anywhere