How to get Formula not value from Worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using XL 2003 & 97

The following code works but it enters on sheet1 the value when I really
want the link to the cell on sheet2

Sheets("Sheet1").Cells(Rows.Count, 10).End(xlUp)(3, 1).FormulaR1C1 = _
Sheets("Sheet2").Cells(Rows.Count, 10).End(xlUp)(1, 1)

I thought the .FormulaR1C1 would have done that.

In short, the code reaches to Sheet2 properly finds and gets $99,999.99

But I want something like ='Sheet2!'A20 then on sheet1 displays $99,999.99
via formula.

TIA Dennis


Sheets("Sheet1").Cells(Rows.Count, 10).End(xlUp)(3, 1).FormulaR1C1 = _
Sheets("Sheet2").Cells(Rows.Count, 10).End(xlUp)(1, 1)
 
The default property for that range is .value. So your expression is equivalent
to:

Sheets("Sheet1").Cells(Rows.Count, 10).End(xlUp)(3, 1).FormulaR1C1 = _
Sheets("Sheet2").Cells(Rows.Count, 10).End(xlUp)(1, 1).Value

If you wanted to copy the formula from one cell to the other--not the same thing
as a link:

Sheets("Sheet1").Cells(Rows.Count, 10).End(xlUp)(3, 1).FormulaR1C1 = _
Sheets("Sheet2").Cells(Rows.Count, 10).End(xlUp)(1, 1).FormulaR1C1

If you really wanted just to point at that other cell:

Sheets("Sheet1").Cells(Rows.Count, 10).End(xlUp)(3, 1).Formula = _
"=" & Sheets("Sheet2").Cells(Rows.Count, 10).End(xlUp).address(external:=true)

The .address(external:=true) includes the sheet name.

I got a formula like:
=Sheet2!$J$1

If I wanted relative references (J1 instead of $J$1), I could change that
..address() portion:

.end(xlup).address(false,false,external:=true)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top