excel vba calculate code doesn't calculate some of the formulas in merged cells

1
votes

I calculate the formulas in different sheets using:

Sub Calculate_Formulas_otherSheets()
Dim Sheet As Worksheet
For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Name Like "*" & strSearch & "_A" Then
         Sheets(Sheet.Name).Calculate
    End If
Next
For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Name Like "*" & strSearch & "_B" Then
         Sheets(Sheet.Name).Calculate
    End If
Next

End Sub


in these sheets, three of the formulas are in merged cells.
formula 1 in AY3

=RIGHT(CELL("filename");LEN(CELL("filename"))-FIND("]";CELL("filename")))


formula 2 in AZ3

=IFERROR(RIGHT(LEFT($AY3;FIND("_";$AY3)-4);2);"")


formula 3 in BA3

=IFERROR(RIGHT(LEFT($AY3;FIND("_";$AY3)-3);1);"")


everything works perfect if i calculate them manually. But when I do it via the above code, they don't get calculated; other formulas in the sheets do get calculated except these three.
What is going wrong?
By the way, the automatic calculation is off.
UPDATE
actually it gets updated in a wrong way: the filename formula gets the sheet name of the sheet in which this vba code is saved as a macro button. How to fix it?

1
vbaexcelexcel-formula
What if you specify the used range in the calculate? Does it work then? – Raystafarian
Provide the second argument to the CELL function, otherwise the information specified in the info_type argument is returned for the last cell that was changed. – GSerg
@GSerg: Thanks! Yes the reference in cell function was missing! It is OK now. – Marcel
@GSerg: put your explanation as the answer to this question and i will accept it for future community use. – Marcel

1 Answers

2
votes

As documented, the second parameter of the CELL function, reference, controls the cell you want information about, and:

If omitted, the information ... is returned for the last cell that was changed.

You are omitting the second parameter, and apparently the last cell that changed happens to be inside a wrong workbook.

Provide a correct cell in the second parameter.