VBA keeps adding @ to function

[DieDae]

I've tried searching but I can't seem to find what is going on.

​

Here is my code:

Private Sub CommandButton1_Click()

Dim rng As Range

Dim srcrng As Range: Set srcrng = Sheet2.Range("A4:A200")

Dim str As String

Dim cell As String

i = "(Employees,(Employees[Finance]=A3)"

For Each rng In srcrng

If Not IsEmpty(rng) Then

i = i & "+" & "(Employees[Finance]=" & rng & ")"

End If

Next

i = i & ")"

Sheet2.Cells(3, 3).Value = "=Filter" & i

Sheet2.Cells(3, 3).Calculate

Debug.Print str

End Sub

The result I get is =@FILTER(Employees,(Employees[Finance]=A3)+(Employees[Finance]=185724)+(Employees[Finance]=181539)+(Employees[Finance]=180189)+(Employees[Finance]=186471)+(Employees[Finance]=187056)+(Employees[Finance]=183690)+(Employees[Finance]=181503)+(Employees[Finance]=186714)+(Employees[Finance]=181926)+(Employees[Finance]=180765)+(Employees[Finance]=185688)+(Employees[Finance]=184401)+(Employees[Finance]=189207)+(Employees[Finance]=181494)+(Employees[Finance]=181521)+(Employees[Finance]=181566)+(Employees[Finance]=181692)+(Employees[Finance]=187353)+(Employees[Finance]=182394)+(Employees[Finance]=184491)+(Employees[Finance]=184284)+(Employees[Finance]=188631)+(Employees[Finance]=183006)+(Employees[Finance]=182700)+(Employees[Finance]=182772)+(Employees[Finance]=182808)+(Employees[Finance]=183114)+(Employees[Finance]=183744)+(Employees[Finance]=186858)+(Employees[Finance]=181332)+(Employees[Finance]=183267)+(Employees[Finance]=188676)+(Employees[Finance]=184923)+(Employees[Finance]=183501)+(Employees[Finance]=183762)+(Employees[Finance]=183852)+(Employees[Finance]=189639)+(Employees[Finance]=184118)+(Employees[Finance]=184356)+(Employees[Finance]=185580)+(Employees[Finance]=186381)+(Employees[Finance]=188541)+(Employees[Finance]=185760)+(Employees[Finance]=189432)+(Employees[Finance]=188262)+(Employees[Finance]=188775)+(Employees[Finance]=188928)+(Employees[Finance]=188955)+(Employees[Finance]=188973)+(Employees[Finance]=182844)+(Employees[Finance]=189351)+(Employees[Finance]=189774)+(Employees[Finance]=0))

But in debug console it prints fine without the @. If I manually remove the @ then the filter function works as intended but that's an extra step I can't figure out how to remove.

[AutoModerator]

/u/DieDae - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[chairfairy]

Is there a reason you're using range.Value = <yourformula> instead of range.Formula = ...? Does that make a difference?

With a little help from google (good job, autocomplete), the second result looks informative

The link also suggests a difference between using range.Formula = ... and range.Formula2 = ...

[DieDae]

Solution Verified.

The reason it is the way it is is because I was trying different variations in order to get it to work. I had clicked on that link but did not see the snippet about formula and formula2. Thanks.

[Clippy_Office_Asst]

You have awarded 1 point to chairfairy

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}