Address Referencing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

In10sJP - 21 Sep 2007 16:32 GMT

When generating a formula for a given cell through VBA, I need to be able to
combine a relative reference (of form of R[x]C[y]) and an address retrieved
from a different sheet (of form Sheet1!A12). When the VBA accomplishes this,
the cell reference is invariably returned as a literal value: 'A12' and no
longer functions as a cell reference. This seems new to Excel 2003 and is
extremely unhelpful. However, any VBA coding I made in Excel 2000 with
exactly this form of coding still functions properly. Can anyone advise me?

Reply to this Message

Chip Pearson - 21 Sep 2007 23:13 GMT

I'm not sure what you're after, but perhaps the following will shed some
light:

Range("C10").Formula = Application.ConvertFormula("=R[-1]C[-2]", _
xlR1C1, xlA1, , relativeto:=ActiveCell) & _
"+" & Range("F1").Address(False, False)

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> When generating a formula for a given cell through VBA, I need to be able
> to
[quoted text clipped - 7 lines]
> exactly this form of coding still functions properly. Can anyone advise
> me?

Reply to this Message