Detect missing number within range

[azhanurul]

Hi Guys,

Recently I manage to find code to detect missing number within range

Range:
Min : 554
Max: 557

Code:
=SMALL(IF(COUNTIF(K2:K100,ROW($K$554:$K$557))=0,ROW($K$554:$K$557),""),ROW())

But the range is hard-coded, how to change from hard-coded to Cell name instead?

I try below but no luck, lets say min range is at cell A2 and max range at cell B2

=SMALL(IF(COUNTIF(K2:K100,ROW($K$(A2):$K$(B2)))=0,ROW($K$(A2):$K$(B2)),""),ROW())

Any clue?

Thanks
Azhan
Malaysia

[Leith Ross]

Hello Azhan,

The easiest way is to create a named range. You can then use that name in the formula. It will make you formula easier to understand and change when needed.

[azhanurul]

Hello Azhan,

The easiest way is to create a named range. You can then use that name in the formula. It will make you formula easier to understand and change when needed.

Thanks for your reply Leith!

I'm not very sure on how to use the "named range". The range is not fixed and the value is change time to time from other Cell's macro.

Let say my min range is in cell A2, A2 value depend on other Cell's macro and that is why I need to define Cell name in above macro instead of hard-coded the value.

[martindwilson]

ah there is a slight flaw in the formula as written ,the end bit row() is to determine the first smallest but this depends on the formula being in row 1
to make it more dynamic you can put in a2 =min(k2:k100) and in b2 max(k2:k100)
or whatever your limits are instead
then use
=SMALL(IF(COUNTIF($K$2:$K$100,ROW(INDIRECT($A$2&":"&$B$2)))=0,ROW(INDIRECT($A$2&":"&$B$2)),""),ROWS($A$1:A1)) array entered and drag down

but now as i see you mention macro?
indirect is not one you can use in vba
INDIRECT() No direct equivalent - use Range,
Cells, Offset, or any other way of
referencing cells

[azhanurul]

ah there is a slight flaw in the formula as written ,the end bit row() is to determine the first smallest but this depends on the formula being in row 1
to make it more dynamic you can put in a2 =min(k2:k100) and in b2 max(k2:k100)
or whatever your limits are instead
then use
=SMALL(IF(COUNTIF($K$2:$K$100,ROW(INDIRECT($A$2&":"&$B$2)))=0,ROW(INDIRECT($A$2&":"&$B$2)),""),ROWS($A$1:A1)) array entered and drag down

but now as i see you mention macro?
indirect is not one you can use in vba
INDIRECT() No direct equivalent - use Range,
Cells, Offset, or any other way of
referencing cells

Nice one wilson...it works like charm!! hehe..

1 last question though, how do i ignore #NUM! value in order to search MAX/MIN range using =MAX(K:K) function ?