Nrthugu

I encountered this issue while working on a monthly report:

When feeding Range.Formula formulae (any kind of formula) with VBA they don't seem to calculate and show only the text of the formula unless I - open them manually cell-by-cell and hit the Enter key or

1. Find-and-Replace the "=" for "="

2. Do Text to Columns on the data

However, none of this works when I try to do any of these actions programatically through VBA i.e.

1. Find-and-Replace works when replacing the "=" in the formula with anything else but when trying to replace it back it stays unchanged

2. Run Text to Columns with no result

I tried both writing my own functions and recording those actions with macro recorder.

Here is the list of things I tried so far:

• Checking the format of the column I am inserting the data into - it is set to Number
  


• Setting the data format from Number to General
  


• Inserting the formulae somewhere else in the document through VBA


• Copying the formulae and inserting it somewhere else on the sheet


• Copying the formulae and inserting it into a different workbook


• Changing the workbook Calculation mode from automatic to manual and back


• Saving the workbook and reopening it


• Forcing recalculation


• Forcing global recalculation


• Setting the Application.Iteration to True


• Setting the Range.Dirty


• Setting ThisWorkbook.ForceFullCalculation to True


• Trying different method of data insertion eg. feeding the range from array to feeding the column row-by-row


• Show formulas is disabled

One thing I have to point out is when I evaluate the formula it says that the formula contains a constant.

Can anybody help me?

Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByVal Destination As Any, ByVal source As Any, ByVal Length As Long) 



Function StringRepeatSimple(number As Long, expression As String) As String

  Dim expressionLengthBytes As Long

  expressionLengthBytes = LenB(expression)



  StringRepeatSimple = Space$(number * Len(expression)) 



  Dim bufferPointer As Long

  bufferPointer = StrPtr(StringRepeatSimple)



  Dim expressionPointer As Long

  expressionPointer = StrPtr(expression)



  Dim copyCounter As Long

  For copyCounter = 0 To number - 1



    CopyMemory bufferPointer + copyCounter * expressionLengthBytes, expressionPointer, expressionLengthBytes



  Next copyCounter 



End Function



Private Sub crtTmpRng(ByVal sumSht As Worksheet, ByVal usdRng As Range)



  itmArr = Split("Akvizice,Distribuce,Doplňkové služby,Last Call,Neutilitní činnosti,Nezařazené činnosti,Ostatní činnosti,Přepisy,Retence,RPG,Smlouvy,Tisky,Výpovědi,Změna zák. dat,ŽoP", ",") 



  For j = LBound(itmArr) To UBound(itmArr)



    Set tmpRng = sumSht.Range(sumSht.Cells(Intersect(usdRng, sumSht.Range("A:A")).Find(itmArr(j)).Row, usdRng.Find(MonthName(Month(Now()) - 1, 0)).Offset(1, 0).Column), _

                              sumSht.Cells(Intersect(usdRng, sumSht.Range("A:A")).Find(itmArr(j) & " Celkem").Row - 1, usdRng.Find(MonthName(Month(Now()) - 1, 0)).Offset(1, 0).Column))



    If IsEmpty(mrgdRng) Then Set mrgdRng = tmpRng Else Set mrgdRng = Union(mrgdRng, tmpRng)



  Next j 



  tmpItmArr = Split(Replace(StringRepeatSimple(258, "ß=IF(ISERROR(VLOOKUP($B3;'09_2018'!$B:$F;3;FALSE));""N"";VLOOKUP($B3;'09_2018'!$B:$F;3;FALSE))"), "ß", "", , 1), "ß") 



  mrgdRng.Formula = tmpItmArr



End Sub



Sub Main()



For Each wbk In Application.Workbooks



  If wbk.Name Like "BC_OKP_##_2018*" Then Exit For



Next wbk

If wbk Is Nothing Then Exit Sub



Set sumSht = wbk.Worksheets("Sum2018")

Set usdRng = sumSht.UsedRange



Call crtTmpRng(sumSht, usdRng)



End Sub

This is more a SuperUser rather than a StackOverflow response.

Try a Ctrl-Shift-Alt-F9 combination or Application.CalculateFullRebuild. I have had this issue - this action rebuilds all the dependencies in the workbook.

In our template workbook we have installed a button on the menu page as an "emergency reset" if code and formula stop working. Application.CalculateFullRebuild is one of the commands that is run from that button.