Filter Expression Help

[Jmal22]

Could someone please tell me why excel giving me #Name when using this expression?

ActiveCell.Formula2 = "=Filter(EnabledwithPriority!A2:DE&RowCount),(EnabledwithPriority!V2:V&RowCount<>""""),"""")"

Excel sees RowCount in this expression as a word and not a row number for column DE.
RowCount is a dynamic number that is changing. I done this before in expressions like Range("A2:DE" & RowCount) but that doesn't work using the filter function.

 

[dreid1011]

You are missing some parenthesis:

ActiveCell.Formula2 = "=FILTER(EnabledwithPriority!A2:DE" & RowCount & "),(EnabledwithPriority!V2:V" & RowCount & "<>""""),"""")"

 

[Jmal22]

You are missing some parenthesis:

ActiveCell.Formula2 = "=FILTER(EnabledwithPriority!A2:DE" & RowCount & "),(EnabledwithPriority!V2:V" & RowCount & "<>""""),"""")"

That worked, thank you!!!

 

[Alex Blakenburg]

That worked, thank you!!!

Did you manually update your formula ? dreid's formula as posted has one too many parenthesis in it and can't possibly have worked.

 

[dreid1011]

I just copied what was originally posted. My bad.

 

[Peter_SSs]

You are missing some parenthesis:

I would argue there are too many rather than not enough, though that is not a particular problem. The main reason for my post is to comment that I generally find it easier to get the right formula format by using placeholder(s) in the formula and then doing one or more 'Replace' actions rather that breaking the formula into lots of pieces to insert the variable value(s).

So the blue part is like the actual formula except with # holding the place(s) where the final row number should be (& the doubled-up quotes of course)
and then Replace just drops in the RowCount value into the relevant place(s)

ActiveCell.Formula2 = Replace("=FILTER(EnabledwithPriority!A2:DE#,EnabledwithPriority!V2:V#<>"""","""")", "#", RowCount)

 

[Alex Blakenburg]

@dreid1011 - I normally get caught out when I do a final tweak directly in the post.
I also find the replace method easier but its good to know how both methods work.

 

[dreid1011]

I would argue there are too many rather than not enough, though that is not a particular problem. The main reason for my post is to comment that I generally find it easier to get the right formula format by using placeholder(s) in the formula and then doing one or more 'Replace' actions rather that breaking the formula into lots of pieces to insert the variable value(s).

So the blue part is like the actual formula except with # holding the place(s) where the final row number should be (& the doubled-up quotes of course)
and then Replace just drops in the RowCount value into the relevant place(s)

ActiveCell.Formula2 = Replace("=FILTER(EnabledwithPriority!A2:DE#,EnabledwithPriority!V2:V#<>"""","""")", "#", RowCount)

Right, I realize now I wrote parenthesis, when I really meant quotation marks... Again, oops. I did write it to a string to make sure I had all the quotes I needed, but I wasn't paying attention to the closing ')' in the middle where it shouldn't have been.

 

[dreid1011]

@dreid1011 - I normally get caught out when I do a final tweak directly in the post.
I also find the replace method easier but its good to know how both methods work.

I just wrote it to a string to make sure I had all the quotes it needed, but didn't pay attention to the pesky ')' in the middle.

 

[dreid1011]

That worked, thank you!!!

You're welcome, and regardless of how you got it to work, I am glad you got it sorted.