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,

 

[BrianM45]

Hi zero269

In my eyes, it is not useful to use such a function in VBA,
it is enough to traverse the lines with a loop, and when a cell is empty, take the value of the cell of the line above +1

Regards
Brian

 

[Peter_SSs]

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

Can you explain why you are trying to 'convert' anything?

 

[James006]

Hi,

To make your life easier, have you tested : Evaluate("yourComplexFormula") ???

 

[zero269]

Hi,

To make your life easier, have you tested : Evaluate("yourComplexFormula") ???

Hi James,

Thanks for the suggestion. I took a look at the VBA Evaluate method, but it didn't like it.
It flagged the LET function. Unfortunately, it's not an available WorksheetFunction object.

 

[zero269]

Can you explain why you are trying to 'convert' anything?

Hi Peter,

Your code works perfectly. However, I was hoping I could move it into VBA to free up cell A1 where it currently resides.
When I would add that value to my list of books, I would enter it manually. Later I took the VBA approach to speed up that process:

'Insert the next available non-AR number into the active cell.
Sub EnterQuizNumber()
    Range("A1").Copy
    ActiveCell.PasteSpecial xlValues
    Application.CutCopyMode = False
    ActiveCell.Offset(1).Select 'Select next cell below
End Sub

This made entering that formulated value easier.

I figure if I could somehow use your formula with my Copy/Paste formula, that would be optimal.
But... LET is being rejected by VBA.

Thanks,

 

[Peter_SSs]

But... LET is being rejected by VBA.

It is not actually rejecting LET, it is just that LET happens to be at the start of where you have a syntax error. That is, where the "Expected: expression" should start.

@James006's question was:

have you tested : Evaluate("yourComplexFormula")

But you have tested
Evaluate(yourComplexFormula)
Can you spot the difference?

Using this sample from that earlier thread (post #3) ..

Zero269_1.xlsm
	C
4	990001
5	990002
6
7	990004
8	990005
9	990006
10	990007
11	990008
12	990009
13	990010
14
Next Available

.. and this code ..

Sub Test()
  Dim NextNum As Long
 
  NextNum = Evaluate("=LET(s,SEQUENCE(1000,,990001),AGGREGATE(15,6,s/ISNA(MATCH(s,C4:C14,0)),1))")
  MsgBox NextNum
End Sub

.. I get this result ..

 

[Dave Patton]

my 2 cents worth
The formula version is more flexible.
Lambda would work but there are few advantages to using it with this challenge see C2

T202303a.xlsm
	C	D	E	F
1
2	990003
3	990003	980009	1004	6
4	990001	980001	1001	1
5	990002	980002	1002	2
6		980003	1003	3
7	990004	980004		4
8	990005	980005	1005	5
9	990006	980006	1006
10	990007	980007	1007
11	990008	980008	1008	8
12	990009		1009	9
13	990010	980010	1010	10
14
1d

Cell Formulas
Range	Formula
C2	C2	=MissingNum()
C3:F3	C3	=LET(s,SEQUENCE(10,,C4,1),AGGREGATE(15,6,s/ISNA(MATCH(s,C4:C14,0)),1))

 

[zero269]

Hi Peter,

Can you spot the difference?

Yes, I can see where I went wrong on trying to use the Evaluate method. I needed to enclose it as written in double-quotes. Lesson learned for sure.
I think this is far easier than the example I saw from Microsoft: Application.Evaluate method (Excel)
It was confusing as hell...

.. and this code ..

I ran your correct version of Evaluate in my workbook and it definitely produced the same value that cell A1 is showing.

The beauty of this is that I can now use your solution for getting the Next Quiz Number and then Pasting it into the Active Cell.

Sub EnterNextQuizNumber()

    Dim NextNum As Long
    NextNum = Evaluate("=LET(s,SEQUENCE(1000,,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

This was extremely helpful!

Thanks again Peter for all your help.

 

[zero269]

The formula version is more flexible.

Hi Dave,
Yes, for most situations I would agree that approach is best. Especially in very large data sets and ranges.
However, for my situation, I only need that value so I can add it back into the worksheet when needed.

Sub EnterNextQuizNumber()

    Dim NextNum As Long
    NextNum = Evaluate("=LET(s,SEQUENCE(1000,,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

Thank you and best regards,