Fabricating Cell Address

[Sonya795]

Hello everyone.

I need your help.
I am programmatically determine the coordinates of needed cell.
For example, I am interested in "=R18C3"
But R18 was found using "=MATCH(""ITEM_ID"",C[-1],0) + 3" THIS EQUALS to 18. So, I know that my desired cell located in row 18.

Is there a way in Excel 97, to concatenate or join this two together. I tried “=R[(MATCH(""ITEM_ID"",C[-1],0) + 3)C[1]”. But it does not work.
I also tried =R&[ MATCH(""ITEM_ID"",C[-1],0) + 3}&C[1]…

Is there a way to fabricate the cell address?

Thank you in advance,

Sonya

[Bryan Hessey]

Sonja,

I don't think you can directly access via R1C1 the way you are trying, but you can use the 'offset' as you calculated to adjust the Offset access, thus
=Offset(A1,18-1,0,1,1) would give cell A18 as would
=Offset(A1,(MATCH(""ITEM_ID"",C[-1],0)+3)-1,0,1,1)

For your stated purpose the Offset is, A1 = reference point, row increment, column increment, 1,1

Hope this helps.

Hello everyone.

I need your help.
I am programmatically determine the coordinates of needed cell.
For example, I am interested in "=R18C3"
But R18 was found using "=MATCH(""ITEM_ID"",C[-1],0) + 3" THIS EQUALS to 18. So, I know that my desired cell located in row 18.

Is there a way in Excel 97, to concatenate or join this two together. I tried “=R[(MATCH(""ITEM_ID"",C[-1],0) + 3)C[1]”. But it does not work.
I also tried =R&[ MATCH(""ITEM_ID"",C[-1],0) + 3}&C[1]…

Is there a way to fabricate the cell address?

Thank you in advance,

Sonya

[Sonya795]

Brain,
Thank you very much,
It works perfectly.
Sonya

[Bob Phillips]

No tested, but try

ActiveCell.FormulaR1C1 = "=R" & Application.Match("ITEM_ID", _
ActiveCell.Offset(0, -1).EntireColumn, 0) + 3 & "C3"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sonya795" <[email protected]> wrote in
message news:[email protected]...
>
> Hello everyone.
>
> I need your help.
> I am programmatically determine the coordinates of needed cell.
> For example, I am interested in "=R18C3"
> But R18 was found using "=MATCH(""ITEM_ID"",C[-1],0) + 3" THIS EQUALS
> to 18. So, I know that my desired cell located in row 18.
>
> Is there a way in Excel 97, to concatenate or join this two together.
> I tried "=R[(MATCH(""ITEM_ID"",C[-1],0) + 3)C[1]". But it does not
> work.
> I also tried =R&[ MATCH(""ITEM_ID"",C[-1],0) + 3}&C[1].
>
> Is there a way to fabricate the cell address?
>
> Thank you in advance,
>
> Sonya
>
>
> --
> Sonya795
> ------------------------------------------------------------------------
> Sonya795's Profile:
http://www.excelforum.com/member.php...o&userid=26110
> View this thread: http://www.excelforum.com/showthread...hreadid=396805
>