how use range to bottom of filled cells?
In VB, there is a way to specify a range to the bottom of filled cells:
.....Range("A65536").End(xlUp).Address Is there something like that in the worksheet functions? I have a cell that has this command: =IF(ISERROR(VLOOKUP($B62,'91 SCTB'!$C$2:$D$570,2,FALSE))=FALSE,VLOOKUP($B62,'91 SCTB'!$C$2:$D$570,2,FALSE),"NO MATCH") And it's kinda ugly, I know, but I want to change the $D$570 to something like the above, where it just searches down to the bottom of the filled cells. The area changes every now and then, and I want it so the search will work even though the user (me or someone else) forgot to change the range in the above function. I guess I could do 65536, but I was wondering if there was something more cleaner. Thanks alot. |
how use range to bottom of filled cells?
=IF(NOT(ISERROR(VLOOKUP($B62,INDIRECT("'91
SCTB'!C2:D"&MAX(IF(ISBLANK($D$1:$D$65535),0,ROW($D $1:$D$65535)))),2,FALSE))) ,VLOOKUP($B62,INDIRECT("'91 SCTB'!C2:D"&MAX(IF(ISBLANK($D$1:$D$65535),0,ROW($D $1:$D$65535)))),2,FALSE)," NO MATCH") it is an array formuila, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Ian Elliott" wrote in message ... In VB, there is a way to specify a range to the bottom of filled cells: ....Range("A65536").End(xlUp).Address Is there something like that in the worksheet functions? I have a cell that has this command: =IF(ISERROR(VLOOKUP($B62,'91 SCTB'!$C$2:$D$570,2,FALSE))=FALSE,VLOOKUP($B62,'91 SCTB'!$C$2:$D$570,2,FALSE),"NO MATCH") And it's kinda ugly, I know, but I want to change the $D$570 to something like the above, where it just searches down to the bottom of the filled cells. The area changes every now and then, and I want it so the search will work even though the user (me or someone else) forgot to change the range in the above function. I guess I could do 65536, but I was wondering if there was something more cleaner. Thanks alot. |
All times are GMT +1. The time now is 04:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com