Macro can't run otherwise working formula

[ladylissa21]

I'm trying to run a short macro to set up an "Audit Selection" population of data. But I always get an error when the script gets to the part of entering the formula:

=LET(d,FILTER('Policy List'!A4:A5000,'Policy List'!A4:A5000<>""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))

Another user on this forum helped me with the formula and it works great when I just paste it in the cell. Is there some reason I can't plug it into a macro?

Audit Selection Macro.txt

Debug.JPGError Screenshot.JPG

[JohnTopley]

As far as I can determine, the "LET" formula et al cannot be incorporated into VBA (but I could be wrong!).

[TMS]

@JT: shouldn’t be a problem, it's just a formula.

@ladylissa21: Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[JohnTopley]

Using FormulaArray places the formula correctly BUtT it does not SPILL as a "native" formula would.

Over to the gurus!

[TMS]

You use Formula2 or Formula2R1C1 with dynamic formulae.

[JohnTopley]

@TMS: thank you ... learned something new!

So hopefully the OP's query has been answered.

example ....

Sub x()
[h33].Formula2 = "=LET(a,M4:Q28,b,M4:M28,FILTER(a,b=""Compi""))"

End Sub

[TMS]

In the absence of a workbook and the other formulae, best guess:

Option Explicit

Sub Test()

Sheets("Sheet1").Range("A2").Formula2 = _
    "=LET(d,FILTER('Policy List'!A4:A5000,'Policy List'!A4:A5000<>""""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))"

End Sub

Sub AuditSelection_TMS()
'
' AuditSelection Macro
'
Dim shNew As Worksheet

' delete the Adjusted List sheet, if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Adjusted List").Delete
Application.DisplayAlerts = False
On Error GoTo 0

' create the Adjusted List sheet
Sheets("Policy List").Copy Before:=Sheets(2)
Set shNew = ActiveSheet

With shNew
    .Name = "Adjusted List"
    .Range("A4:A" & .Range("A" & .Rows.Count).End(xlUp).Row).ClearContents
    .Range("C4:C" & .Range("C" & .Rows.Count).End(xlUp).Row).ClearContents
    .Range("F4:F" & .Range("F" & .Rows.Count).End(xlUp).Row).ClearContents
    .Range("A4").Formula2 = _
        "=LET(d,FILTER('Policy List'!A4:A5000,'Policy List'!A4:A5000<>"""",""no data""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))"
    .Range("C4").Formula2 = _
        "=LET(d,FILTER('Policy List'!C4:C5000,'Policy List'!C4:C5000<>"""",""no data""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))"
    .Range("F4").Formula2 = _
        "=LET(d,FILTER('Policy List'!F4:F5000,'Policy List'!F4:F5000<>"""",""no data""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))"
    
    ' convert to values
    With .Range("A4:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Value = .Value
    End With '.Range()
End With 'shNew

End Sub

[TMS]

Very strange. If I use the macro in post #7, the formulae are correctly output to the sheet.

If I then record a macro while I re-enter the formula, this is what I get:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
Range("A4").Select
ActiveCell.Formula2R1C1 = _
    "=LET(d,FILTER('Policy List'!RC:R[4996]C,'Policy List'!RC:R[4996]C<>"""",""no data""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))"
Range("C4").Select
ActiveCell.Formula2R1C1 = _
    "=LET(d,FILTER('Policy List'!RC:R[4996]C,'Policy List'!RC:R[4996]C<>"""",""no data""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))"
Range("F4").Select
ActiveCell.Formula2R1C1 = _
    "=LET(d,FILTER('Policy List'!RC:R[4996]C,'Policy List'!RC:R[4996]C<>"""",""no data""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))"
Range("F5").Select
End Sub

If I then try to run that (on an empty sheet), it fails, as per the OP.

[TMS]

This appears to be a workaround, but I'm sure there must be a better way.

Sub Macro2()
'
' Macro1 Macro
'

'
Range("A4").Select
ActiveCell.FormulaArray = _
    "=LET(d,FILTER('Policy List'!RC:R[4996]C,'Policy List'!RC:R[4996]C<>"""",""no data""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))"
ActiveCell.Formula2 = ActiveCell.Formula2
Range("C4").Select
ActiveCell.FormulaArray = _
    "=LET(d,FILTER('Policy List'!RC:R[4996]C,'Policy List'!RC:R[4996]C<>"""",""no data""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))"
ActiveCell.Formula2 = ActiveCell.Formula2
Range("F4").Select
ActiveCell.FormulaArray = _
    "=LET(d,FILTER('Policy List'!RC:R[4996]C,'Policy List'!RC:R[4996]C<>"""",""no data""),r,ROUNDUP(ROWS(d)*50%,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))"
ActiveCell.Formula2 = ActiveCell.Formula2
Range("F5").Select
End Sub

[JohnTopley]

Odd ... when I tried FormulaArray on my test it did not work i.e did not spill - whereas Formula2 did!!!

[TMS]

Formula2 with an A1 formula works. Formula2R1C1 with an R1C1 formula errors. I don't know why.

FormulaArray with R1C1 formula "works" but doesn’t Spill. It outputs it as an Array Entered formula. But, having got it into the cell, .Formula2 = .Formula does work and Spills.

I don't usually use R1C1 format formulae so I haven't encountered this behaviour.

[LadyLissa82]

So sorry - I got locked out for a little while there! I've attached a workbook mockup with dummy information:

Workbook Example.xlsm

Is that what is needed?

[LadyLissa82]

Actually, I think the solution in post #7 works! I plugged it in to my original workbook and it's doing what I need it to. Thank you both so much!

[TMS]

Actually, I think the solution in post #7 works! I plugged it in to my original workbook and it's doing what I need it to. Thank you both so much!

You're welcome. Thanks for the rep.

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.