I will paste here my VBA code which is supposed to add and calculate a formula in a given range. I am using a Max If formula in my master page which technically is supposed to retrieve the last contact date in another sheet.
Sub LastContactDate()
Dim WsMaster As Worksheet
Dim WsLastRow As Long
Dim OutputRange As Range
Dim tmpCalc As XlCalculation: tmpCalc = Application.Calculation 'Save setting
Const OutputColumn As Long = 29 ' "AC"
Set WsMaster = ThisWorkbook.Worksheets("Master")
WsLastRow = WsMaster.Range("A" & Rows.Count).End(xlUp).Row
Set OutputRange = WsMaster.Range(WsMaster.Cells(5, OutputColumn), WsMaster.Cells(WsLastRow, OutputColumn))
Application.Calculation = xlCalculationManual 'Makes things slightly faster
WsMaster.Unprotect
With OutputRange
.Formula = "=IF(MAX(IF(ContactDate!A:A=Master!B5,ContactDate!B:B))=0,"""",MAX(IF(ContactDate!A:A=Master!B5,ContactDate!B:B)))" 'This will fill down automatically
' .Replace What:="@", Replacement:="", LookAt:=xlPart, _
' SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
' ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
.Calculate 'Needed because Calculation is currently manual
' .Value = .Value 'Convert the formulae into flat values
.HorizontalAlignment = xlCenter
.Font.Color = RGB(0, 32, 96)
.Interior.Color = RGB(221, 235, 247)
.Font.Bold = False
.Font.Size = 11
.Font.Name = "Calibri"
.NumberFormat = "dd/mm/yyyy"
End With
Application.Calculation = tmpCalc 'Restore setting saved earlier
End Sub
The formula is added into the master page column but is paste incorrectly as follows:
=IF(MAX(IF(**@**ContactDate!A:A=Master!B5,ContactDate!B:B))=0,"",MAX(IF(**@**ContactDate!A:A=Master!B5,ContactDate!B:B)
Why the @ sign is added? How can I get rid of it?
If I use the Replace part the file becomes very slow.
Thank you