0

All of a sudden Excel 365 started to add an @ to my formula which is assigned via VBA. The documentation says it isn't supposed to affect functionality but IT DOES and gives me #VALUE! error. The formula is correct without it when I manually remove it. The documentation also says this comes from structured names when using a table but the data is not in a table.

I've tried everything I can think of to remove it and nothing works. I've assigned a SENDKEYS sequence to FixFormula() and call it in my proc but it doesn't work. If I assign FixFormula() to a button on the sheet, it works fine.

My question here is:

  1. Can I turn off Excel adding the @ to the formulas; or
  2. How can I fix the SENDKEYS call to work within my code?

Here is the section of code:

'lookup/get values from CW Download and copy to KPMG
CWLastRow = Sheets("CW Download").Cells(Sheets("CW Download").Rows.Count, "B").End(xlUp).Row

vLookupArray = "'CW Download'!$Z$2:$Z$" + CStr(CWLastRow)
vReturnArray = "'CW Download'!$A$2:$R$" + CStr(CWLastRow)
 
Range("AP2").Formula = "=XLOOKUP($Z2," + vLookupArray + "," + vReturnArray + ",""-"",0)"

'remove @ symbol from formula
Range("AO2").Formula = "=FORMULATEXT(AP2)"
If Mid(Range("AO2"), 2, 1) = "@" Then
    Sheets("KPMG").Activate
    Range("AP2").Activate
    Call FixFormula
End If
Range("AP2").AutoFill Destination:=Range("AP2:AP" + CStr(pLastRow)), Type:=xlFillDefault

Sub FixFormula()

    SendKeys "{F2}{UP 15}{HOME}{RIGHT}{DEL}{ENTER}"
    
End Sub

This is a snippet from a complete automation project and has me pulling my hair out. Everything worked fine until this "feature" started showing up earlier this week. Research says it was added in the 2019 release and ought not to affect functionality.

This is a snippet from another proc in the project, also assigning an XLOOPUP formula and this issue doesn't arise even though it is putting the formula between 2 pivot tables on that Worksheet.

pLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Cells(4, 8).Value = "Match"
Cells(5, 8).Formula = "=XLOOKUP($J5,$A$5:$A$" + CStr(pLastRow) + ",$G$5:$G$" + CStr(pLastRow) + ",0,0)"
Cells(4, 9).Value = "Variance"
Cells(5, 9).Formula = "=$M5-$H5"

There is not a table on Worksheet 'CW Download' or 'KPMG' where the code is running on.

Any insight is much appreciated.

Debbi Handler
  • 1

0 Answers0