Using a variable or object to reference the range in a formula array. 1

[Stevo911]

Background

simple eg.

A B C
apple pear FALSE
grape apple TRUE
orange pineapple FALSE

Using formula array:
={OR(EXACT(B2,A2:A4))}

and fill-down in column C to get answers.

My Question:
For different lengths of column A, how can one use an input box, lets say row_num = application.inputbox("how many rows?") and then substitute this into the formula.

eg. Selection.FormulaArray = "=OR(EXACT(RC[-1],*some reference using row_num*))"

I've tried to reference the cells using index numbers, and A1 notation but VBA doesnt like my efforts.

Any help would be GREATLY appreciated.
Thanks

 

[SkipVought]

Hi,

Check out

How can I rename a table as it changes size faq68-1331

The OFFSET function is an extremely powerful tool.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}