Fabricating Cell Address
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 |
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. Sonya795 Wrote: 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 ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=396805 |
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" wrote in message ... 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 |
Brain, Thank you very much, It works perfectly. Sonya -- Sonya795 ------------------------------------------------------------------------ Sonya795's Profile: http://www.excelforum.com/member.php...o&userid=26110 View this thread: http://www.excelforum.com/showthread...hreadid=396805 |
All times are GMT +1. The time now is 02:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com