0

I have the following piece of VBA code which is inserting a formula into a column on one of my worksheets. The formula works fine when used in Excel normally (without inserting it using VBA), however when I insert it using the VBA code, Excel is automatically adding @ operators which are causing the formula to return an error value every time. If I manually remove the @ operators from the formula which has been created in the sheet by, the formula works again.

How do I ensure these @ operators are either not automatically added? Or how do I change what I'm doing so the @ operators don't cause issues?

FYI, the formula is a simple index-match formula to match variables to a master table in order to return the SKU (unique code) for the item. The master table is on a tab named 'SKU', and the variables to match are on the 'Received - Open' tab.

Worksheets("Received - Open").Range("AG2:AG" & lastrow).Formula = "=INDEX(SKU!$A$2:$G$10000,MATCH('Received - Open'!AK2&'Received - Open'!J2&'Received - Open'!AH2&'Received - Open'!AI2&'Received - Open'!AC2,SKU!$C$2:$C$10000&SKU!$D$2:$D$1000&SKU!$E$2:$E$1000&SKU!$F$2:$F$1000&SKU!$G$2:$G$1000,0),1)"

Many thanks in advance!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dan Kidney
  • 143
  • 1
  • 11
  • The formulaLocal should help you. .Range("AG2:AG"" & lastrow).FormulaLocal – snenson Apr 14 '21 at 09:37
  • 1
    You should probably use `.Formula2`. Please report back if this is a duplicate of [this](https://stackoverflow.com/q/61138029/9758194) question. – JvdV Apr 14 '21 at 09:39
  • 1
    Hi @JvDv, I have tried your solution and it worked perfectly thank you. I have also just read the other post you've tagged and it appears to be the exact same issue. Thanks for clarifying. – Dan Kidney Apr 14 '21 at 10:29

0 Answers0