Problem with Dynamic Range in VBA Formula

[zaska]

Hello,

Range("I2:I" & LR).Formula = "=G2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&'[NSE Converter.xls]Sheet1'!$A$1:$A$233,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),'[NSE Converter.xls]Sheet1'!$B$1:$B$233)"

In the above formula the No.of Rows is Dynamic sometimes it is 233 and sometimes 239 etc.,

Can anyone help me to handle this situation?

 

[jim may]

assign them a named range

 

[zaska]

Sir,

Thank you but could you help me how to use named range?

 

[jim may]

http://support.microsoft.com/kb/830287

 

[zaska]

Hi,

Formula:


Range("I2:I" & LR).Formula = "=G2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&'[NSE Converter.xls]Sheet1'!$A$1:$A$233,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),'[NSE Converter.xls]Sheet1'!$B$1:$B$233)"


This formula is lookingup Sheet1 of NSE Converter.xls and the columns A and B are dynamic i.e sometimes it is 233 , 256 , 211 et.,

Can anyone kindly help me as to how could i use Offset formula in the above situation.

Thank you