Sequence in VBA

[randymone]

I can use a filter () function to get a specific value from a column in an excel table using filter(index(table, sequence(row(table)),{4}),criteria) in excel. where table = the table name and {4} is the table column for the result. I would like to convert this formula from excel to a vba process. The issue i am having is that curly bracket surrounding the 4 is identified as an invalid character in VBA. Any suggestions?
TIA.
Randy

 

[Fluff]

Just get rid of the {} as you do not need them for a single column.

 

[randymone]

Just get rid of the {} as you do not need them for a single column.

Thanks for the response. It eliminated that issue, I am still having issues converting the filter() function from an excel formula to vba. Is this possible? I tried to use the evaluate function creating a string to test in an excel sheet (it worked), but using evaluate resulted in Error 2015. Here is the text sting

=FILTER(INDEX($R$3:$AC$110, SEQUENCE(ROWS($R$3:$AC$110)), 12), ($R$3:$R$110 = 62.1) * ($S$3:$S$110= "S")* ($AB$3:$AB$110 = ""))

and the vba code.

Evaluate("Filter(Index(" & tbldata & ", Sequence(Rows(" & tbldata & ")), 4), (" & cuid & " = " & CurrID & ") * (" & oid & "= " & qt & coid & qt & ")" & "* (" & DEL & " = """"))")

Any guidance would be appreciated. Let me know if you have any other questions.

Thanks!

Randy

 

[Fluff]

Without knowing what your data is like, or what all those variables are it's impossible to help.

 

[Skyybot]

Thanks for the response. It eliminated that issue, I am still having issues converting the filter() function from an excel formula to vba. Is this possible? I tried to use the evaluate function creating a string to test in an excel sheet (it worked), but using evaluate resulted in Error 2015. Here is the text sting

=FILTER(INDEX($R$3:$AC$110, SEQUENCE(ROWS($R$3:$AC$110)), 12), ($R$3:$R$110 = 62.1) * ($S$3:$S$110= "S")* ($AB$3:$AB$110 = ""))

and the vba code.

Evaluate("Filter(Index(" & tbldata & ", Sequence(Rows(" & tbldata & ")), 4), (" & cuid & " = " & CurrID & ") * (" & oid & "= " & qt & coid & qt & ")" & "* (" & DEL & " = """"))")

Any guidance would be appreciated. Let me know if you have any other questions.

Thanks!

Randy

Hi,

The short answer is yes. You can do almost everything that worksheet formulas are capable of with VBA code. But, if you're just starting to use VBA code after a long time of using formulas, you need to change the way you think about manipulating the data. Often, in VBA, filtering data is done using Loops.