1

I have a VBA formula that works when I use the ActiveCell.Formula2R1C1 function, which I used to test that I could get the result I want. This works as it should, giving me a comma delimited list.

ActiveCell.Formula2R1C1 =      "=SUBSTITUTE(ARRAYTOTEXT(FILTER(VendSelect1,VendSelect3=LEFT(R[-7]C[-3],6))),LEFT(R[-7]C[-3],6) & "","","""")"

VendSelect1 and VendSelect3 are named ranges from a table in another tab

I am wanting to use the result of this formula as a string to use in a Data Validation list. The reason to go this route is the list will be dynamic based on a value that is entered in another cell, so the list will vary. I have the code for creating the Data Validation working, I just need to get the result of the above equation to be stored as a variable for that code.

I have tried using the Evaluate function to store the result but when I try to put that value in a cell to test that it is working, I get a #VALUE error in the cell. Is this just more complicated a formula than Evaluate can handle? Or am I doing something wrong? I am open to other suggestions to accomplish the same thing but I'm not a VBA expert and this has been the best thing I have found, so far, to accomplish this. Here is the code I have that is giving me the #VALUE error in cell D14.

Sub Test()
Dim FormTest As Variant
FormTest = Evaluate("=FILTER(VendSelect1,VendSelect3=LEFT(R[-7]C[-3],6))")
Range("D14") = FormTest
End Sub

I've been trying to get this to work for about 2 days and this seems to be the last obstacle to getting what I am wanting to do.

Thank you, in advance.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
slien
  • 11
  • 1
  • 2
    you need to use A1 not R1C1 in Evaluate. – Scott Craner Apr 22 '21 at 23:02
  • 4
    When you use relative cell addresses with Evaluate how would it know *relative to what* ? That's why you need A1 format. Also you really need to use the `Worksheet.Evaluate` version instead of the (default) `Application.Evaluate` (which uses the active sheet for the context) – Tim Williams Apr 22 '21 at 23:06
  • Also, you don't need the "=" at the beginning of the formula (but if it's there doesn't bother) – stenci Sep 08 '22 at 19:57

0 Answers0