vba to find the first unused value in a range

Hi,

First of all they don't look like numbers. They look like text strings which contain numeric digits.
Hence to perform arithmetic they'll need to be converted to numbers.

One way would be the following ARRAY formula entered with Ctrl Shift Enter

Formula:

Please Login or Register  to view this content.

Thanks for the reply Richard,

I need to be able to find this value so that I can use it in an existing vba procedure. I'm don't know if the array formula helps there. (or if it does i don't know how to apply it to vba)

The numbers are numbers. I have them custom formatted as "000"

EDIT:

So I can set up the array as a helper cell, for vba to reference, which will be fine.
But the formula is returning a value of 4 when using the same range & values as I posted above

I changed the range location if that would effect it? =SUM(--(VALUE(B2:B11)=(ROW(2:11))))+1

Hi,

You don't need to change the ROW(1:10) element since you're wanting to compare numbers starting with 1 in B2 with a comparison number 1 which is generated by the Row(1) bit of the Row (1:10)

Hence

Formula:

Please Login or Register  to view this content.

Hello hemiceni,

If you are still interested in how to do this using VBA, here is function that will return the missing value or Empty if there are no missing values.

This function takes the column range to be examined and the difference between the terms. In this example range is "A1:A10" of Sheet1 and the term difference is one.

Macro to Find Missing Value in a Sequence

Please Login or Register  to view this content.

Example Macro for FindMissingValue

Please Login or Register  to view this content.