VBA: Convert Complex Formula to VBA

[zero269]

Hello,

I'm looking to see if I can convert a complex formula that I got from @Peter_SSs in this thread:

Find First Missing Number > 990000

Hello everyone, I've been playing around with a few options that allow people to find missing numbers within a range of numbers. One example is this one: =SMALL(IF(ISNA(MATCH(ROW(A$1:A$30),A$1:A$30,0)),ROW(A$1:A$30)),ROW(A1)) The example above requires all values to be returned in a...

www.mrexcel.com

I was looking into VBA's WorksheetFunction's but LET isn't listed.
This led me to look at one of the other three formulas in that thread. @Phuoc uses just the AGGREGATE function, but after testing it again, I realized it's not finding the first missing number.
So, perhaps there's a way to still use the one provided by Peter?

=LET(s,SEQUENCE(1000,,990001),AGGREGATE(15,6,s/ISNA(MATCH(s,B4:B14,0)),1))

Or perhaps there's another way to achieve the same results using VBA?

Thank you,

 

[Peter_SSs]

You're welcome. Glad it was useful.

BTW, instead of hard-coding that 1000 in the sequence function, which is likely much larger than required anyway, and since you are using a formal table structure, you could use this instead as it should give exactly the right length sequence.

NextNum = Evaluate("=LET(s,SEQUENCE(ROWS(tblBooks),,990001),AGGREGATE(15,6,s/ISNA(MATCH(s,tblBooks[Quiz],0)),1))")

 

[zero269]

You're welcome. Glad it was useful.

BTW, instead of hard-coding that 1000 in the sequence function, which is likely much larger than required anyway, and since you are using a formal table structure, you could use this instead as it should give exactly the right length sequence.

NextNum = Evaluate("=LET(s,SEQUENCE(ROWS(tblBooks),,990001),AGGREGATE(15,6,s/ISNA(MATCH(s,tblBooks[Quiz],0)),1))")

Thanks Peter,

I meant to ask you about that 1000 value earlier to determine if I would run out of numbers when I reach a thousand, but it slipped my mind.

Changes made and tested; works perfect.

Thanks again Peter...

Sub EnterNextQuizNumber()

    Dim NextNum As Long
    NextNum = Evaluate("=LET(s,SEQUENCE(ROWS(tblBooks),,990001),AGGREGATE(15,6,s/ISNA(MATCH(s,tblBooks[Quiz],0)),1))")
    ActiveCell.Value = NextNum
    ActiveCell.Offset(1).Select 'Select next cell below
    
End Sub