Can't get a particular formula to copy down in VBA

[salt_wagonner]

I'm having trouble figuring out what's going on with my macro. I want to copy down a formula that identifies non-consecutive duplicate cells, but when I run the macro I get an "application-defined or object-defined error" message. I know the formula works as-written, because when I manually enter and copy it down it works as expected. I also know the macro works in terms of copying down formulas, because it works fine with other simple formulas. It's something specific to this formula that just won't work.

The values I'm checking are in column C, and I am trying to insert the formula in Column U.

Any help would be appreciated!

Sub CopyDuplicateFormula()
    S_Data.Activate
    LR = S_Data.Cells(Rows.Count, "C").End(xlUp).Row

    S_Data.Range("U2:U" & LR).Select
    Selection.FormulaR1C1 = "=LET(a,C2=$C$2:$C$6915,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),e,INDEX(c,1)+d-1,f,INDEX(c,d),IF(f<>e,TRUE,FALSE))"


End Sub

 

[Joe4]

Note that the way you write formulas on your spreadsheet verseus how you write them in VBA may not be the same, especially if you are using "FormulaR1C1" instead of "Formula".
If you use "FormulaR1C1", "c" is a relative column reference, not a named range or variable.

It looks like maybe you should be using "Formula" instead, i.e.

S_Data.Range("U2:U" & LR).Formula = "=LET(a,C2=$C$2:$C$6915,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),e,INDEX(c,1)+d-1,f,INDEX(c,d),IF(f<>e,TRUE,FALSE))"

If that still does not work, then let Excel do the work for you!
Turn on your Macro Recorder and record yourself entering the formula manually into cell U2.
Then stop the Macro Recorder, and copy the formula potion of your recorded code into your VBA code.

 

[Peter_SSs]

There is no need to select the range to enter formulas in it and selecting can slow your code.
Try this

S_data.Range("U2:U" & LR).Select
S_data.Range("U2:U" & LR).Formula2 = "=LET(a,C2=$C$2:$C$6915,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),e,INDEX(c,1)+d-1,f,INDEX(c,d),IF(f<>e,TRUE,FALSE))"

Edit:
BTW, the last part of your formula can be written more simply

S_data.Range("U2:U" & LR).Formula2 = "=LET(a,C2=$C$2:$C$6915,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),e,INDEX(c,1)+d-1,f,INDEX(c,d),IF(f<>e,TRUE,FALSE))"
S_data.Range("U2:U" & LR).Formula2 = "=LET(a,C2=$C$2:$C$6915,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),e,INDEX(c,1)+d-1,f,INDEX(c,d),f<>e)"

.. and when using a LET function there isn't much point (in my view) of introducing a 'name' and defining it it if you then only use it once later in the formula. You might as well just use the definition itself where you have used that name. For example "e" and "f" in your formula are only used once after their definition so to me it makes more sense, and the formula shorter, if you eliminate those definitions

S_data.Range("U2:U" & LR).Formula2 = "=LET(a,C2=$C$2:$C$6915,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),e,INDEX(c,1)+d-1,f,INDEX(c,d),f<>e)"
S_data.Range("U2:U" & LR).Formula2 = "=LET(a,C2=$C$2:$C$6915,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),INDEX(c,d)<>INDEX(c,1)+d-1)"

 

[Peter_SSs]

Ah, I have been referring to it as "your formula", but I see now where it came from. How to identify duplicate values only if they appear inconsecutively?

Could it be that with my suggestion in that other thread the copy-down process may no longer be required?

 

[salt_wagonner]

Ah, I have been referring to it as "your formula", but I see now where it came from. How to identify duplicate values only if they appear inconsecutively?

Could it be that with my suggestion in that other thread the copy-down process may no longer be required?

Yes, you're right! I apologize if it came off that it was my work, I appreciate your help and certainly didn't intend to offend!

The solution you added in the other thread works beautifully. I've been trying to set it up in the VBA script but am having trouble and getting an "Method 'Formula' of object 'Range' failed" error. The VBA now looks like this:

Sub CopyDuplicateFormula()
    S_Data.Activate

    S_Data.Range("U2").Formula = "=LET(r,C2:C6500,u,UNIQUE(r),rw,ROW(r),FILTER(u,(XLOOKUP(u,r,rw,,,-1)-XLOOKUP(u,r,rw)+1)<>COUNTIF(r,u),""))"

End Sub

Ultimately, my plan is to assign a macro to a button that checks for duplicates in the data set, and alerts the user via msgbox if duplicates are found. Then, this formula would identify the duplicates in the dataset upon further investigation.

You've been a tremendous help thus far, thanks so much for your time and effort.

 

[salt_wagonner]

Note that the way you write formulas on your spreadsheet verseus how you write them in VBA may not be the same, especially if you are using "FormulaR1C1" instead of "Formula".
If you use "FormulaR1C1", "c" is a relative column reference, not a named range or variable.

It looks like maybe you should be using "Formula" instead, i.e.

S_Data.Range("U2:U" & LR).Formula = "=LET(a,C2=$C$2:$C$6915,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),e,INDEX(c,1)+d-1,f,INDEX(c,d),IF(f<>e,TRUE,FALSE))"

If that still does not work, then let Excel do the work for you!
Turn on your Macro Recorder and record yourself entering the formula manually into cell U2.
Then stop the Macro Recorder, and copy the formula potion of your recorded code into your VBA code.

Thanks for commenting! I will try the macro recorder method!

 

[Peter_SSs]

 S_Data.Range("U2").Formula = "=LET(r,C2:C6500,u,UNIQUE(r),rw,ROW(r),FILTER(u,(XLOOKUP(u,r,rw,,,-1)-XLOOKUP(u,r,rw)+1)<>COUNTIF(r,u),""))"

There are two things wrong with your code line.

• As I showed in post #3 above, it needs to be .Formula2 not .Formula
• When quote marks are involved in a formula being inserted by vba, the quote marks need to be doubled up.

So try something like this instead.

Sub NotifySplitData()
  Dim S_Data As Worksheet
  Dim LR As Long
  
  Set S_Data = Sheets("Data")  '<- Check worksheet name
  
  S_Data.Activate
  LR = Range("C" & Rows.Count).End(xlUp).Row
  Range("U2").Formula2 = "=LET(r,C2:C" & LR & ",u,UNIQUE(r),rw,ROW(r),FILTER(u,(XLOOKUP(u,r,rw,,,-1)-XLOOKUP(u,r,rw)+1)<>COUNTIF(r,u),""""))"
End Sub