CODEWITHSUNDEEP
excelvba
A.N.
A.N.

Reputation: 590

Excel VBA keeps returning formula with "@" when referencing sheets

I have an Excel VBA script that does an index/match across two separate sheets. The issue I'm facing is that it codes the formula in my spreadsheet using "@" and it doesn't end up picking the values. I have to go in and manually delete the @ sign so that the formula works.

Does anyone have any suggestion how I can remove the @ symbol?

Code below:

 lws.Range(lRangeAddress).Formula = "=INDEX('" _
                        & qwsName & "'!$C$2:$C$10000,MATCH(1,($F4='" _
                        & qwsName & "'!$B$2:$B$10000)*($G4='" _
                        & qwsName & "'!$A$2:$A$10000),0))"

The formula it returns:

=INDEX('Lever 2Query2'!$C$2:$C$10000,MATCH(1,($F5=@'Lever 2Query2'!$B$2:$B$10000)*($G5=@'Lever 2Query2'!$A$2:$A$10000),0))

Upvotes: 0

Views: 24

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Try using Formula2, please:

lws.Range(lRangeAddress).Formula2 = "=INDEX('" _
                        & qwsName & "'!$C$2:$C$10000,MATCH(1,($F4='" _
                        & qwsName & "'!$B$2:$B$10000)*($G4='" _
                        & qwsName & "'!$A$2:$A$10000),0))"

Upvotes: 1

Related Questions