0

I have the following code in VBA (Excel 365):

Range("B5").Value = "=Jan!T28:T46"

Writing manually "=Jan!T28:T46" in B5 inserts all the values from Jan!T28:T42 to B5:B23 perfectly :) But the same assignment in VBA using the above code makes Excel insert '@' before the reference: '=@Jan!T28:T46'. This '@' symbol results in "#value!" instead of all the values spreading from B5 to B23. I can solve the problem with a simple for loop - but I really wish to get rid of this unwanted and surprising auto completion. Any ideas?

Michal Cohen
  • 11
  • 1
  • 2
  • Try `Range("B5").Formula = "=Jan!T28:T46"` – Darrell H Jan 05 '21 at 18:29
  • `nwanted and surprising auto completion` - in fact, this is in place to *preserve* the behaviour that you would get when running that VBA code in Excel 2016 and older. What has changed is the formula bar, it now interprets the entered formula differently than in Excel 2016 and older. The `Value` and `Formula` properties maintained their exact old behaviour, but under the *new formula bar rules*, the old formulas now have to have the `@` *when displayed in the new formula bar*. – GSerg Jan 05 '21 at 18:35

0 Answers0