0

I am writing a VBA code to input a formula into G4 and autofill to the last row of data in column G. When I run the sub, it adds an @ symbol into the formula that is not supposed to be there. If I manually delete the @, the formula works perfectly. How do I stop this from being added? Code is as follows:

Range("G4:G" & lr).Formula = "=INDEX('[Academic Codes List.xlsx]List'!$A$2:$A$411, MATCH(1, COUNTIF(F4, ""*"" & '[Academic Codes List.xlsx]List'!$A$2:$A$411&""*""), 0))"

The intended formula is:

=INDEX('[Academic Codes List.xlsx]List'!$A$2:$A$411, MATCH(1, COUNTIF(F4, ""*"" & '[Academic Codes List.xlsx]List'!$A$2:$A$411&""*""), 0))

The resultant formula from running the sub is:

=INDEX('[Academic Codes List.xlsx]List'!$A$2:$A$411, MATCH(1, COUNTIF(F4, "*"&@'[Academic Codes List.xlsx]List'!$A$2:$A$411&"*"), 0))

The ghost @ symbol is next to the first ampersand.

Thanks in advance for any suggestions.

Nathan Thomas
  • 1
  • 1
  • Try: `.FormulaArray` instead of `.Formula` – Scott Craner Sep 23 '20 at 16:37
  • 3
    or: `.Formula2` Realize that `.Formula2` is for Office 365 only. If this is being ported to other non office 365 use the option above. – Scott Craner Sep 23 '20 at 16:37
  • .FormulaArray produced an error saying "you cant change part of an array" .Formula2 worked like a charm. Thank you sir! – Nathan Thomas Sep 23 '20 at 16:40
  • See [this](https://stackoverflow.com/q/61138029/9758194). I marked it as a duplicate if you don't mind. – JvdV Sep 23 '20 at 16:40
  • 1
    If this will used in non office 365 versions. then you will need to iterate the cells in column G and insert the formula using `.FormulaArray` one at a time. – Scott Craner Sep 23 '20 at 16:43
  • JvdV, no problem, I searched with very similar terminology to the title you linked to, and it did not come up, so thank you for sending me there! – Nathan Thomas Sep 23 '20 at 16:43

0 Answers0