1

I generate a match formula programmatically (VBA, via a string inserted into the cell) as thus:

=IF((MATCH(1,--('Full Lease Term'!I2:I2000>0),0))/12="","",((MATCH(1,--('Full Lease Term'!I2:I2000>0),0))-1)/12)

(Having also tested this formula manually, I know it works!)

When it is inserted in the cell, excel inserts the '@' symbol prior to the worksheet name, giving me:

=IF((MATCH(1,--(@'Full Lease Term'!I2:I2000>0),0))/12="","",((MATCH(1,--(@'Full Lease Term'!I2:I2000>0),0))-1)/12)

This formula always returns an '#N/A' error, until I manually remove the '@'.

Could someone advise either how to stop excel inserting this symbol, or how to work around?

Hopefully this questions contains sufficient information - but please ask if further clarification required.

TKCZBW
  • 15
  • 4
  • The `@` character would mean that you are referring to a cell or a column in a table so you need to examine your spreadsheet to see how your VBA code is interacting with existing tables. – Michal Rosa Jun 20 '20 at 10:02
  • Thanks, that was my first thought - but I can confirm there are no tables present in the sheets, just standard ranges. – TKCZBW Jun 20 '20 at 10:25
  • 1
    I have closed this as a duplicate. Please let me know if it answers your question. If not, I'll reopen the thread, ok? – JvdV Jun 20 '20 at 10:29
  • Hi there, thanks - I have followed the advice in the post, but altering the construction of the cell from Formula to Formula2 does not help. WHilst the '@' symbol is now gone, the formula still throws an '#N/A' error. – TKCZBW Jun 20 '20 at 10:50
  • That would indicate a match cannot be found and is a different problem altogether. Should I reopen? – JvdV Jun 20 '20 at 11:00
  • @JdV - That's going to be a 'mea culpa' from me - on fishing through all the data some numbers had shifted and it was now generating a 'real' '#N/A' error. Will update & edit accordingly. Many thanks for the answer, and your patience. – TKCZBW Jun 20 '20 at 11:08

0 Answers0