Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Constructing Address formulas

  Asked By: Kevin    Date: Oct 08    Category: MS Office    Views: 565
  

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?

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Kristen Chavez     Answered On: Oct 08

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'

 
Answer #2    Answered By: Jennie Harris     Answered On: Oct 08

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.




Tagged:      

 
 
 
 

Related Post