Excel 2003 formula not calculating after changing it in VBA

Question

I'm using Excel 2003 version.

If I change a formula with VBA such as:

Range("A1").Formula = "=Sheet2!B:B"

sometimes the formula calculates and shows the correct value from cell B1 of Sheet2. However, sometimes it does not. Instead, what shows is still "=Sheet2!B:B" in cell A1 in Sheet1, when it should be the value of cell B1 in Sheet2. Plus, even if I change the format of cell A1 to "General", every time I assign a formula to it via VBA, the format automatically becomes "Text" again.

None of recalculation shortcut keys like F9 or Shift-F9 or CTRL-ALT-SHIFT-F9 work.

Has anyone experienced this problem before? And has anyone found the solution?

Answer 1

Try this

Sub Sample()
    With Range("A1")
        .NumberFormat = "General"
        .Formula = "=Sheet2!B1"
    End With
End Sub

Answer 2

Sounds like the format for that cell is getting changed to Text somehow (possibly in another part of your code).

Try putting these lines before your .Formula = ... line

Sub test()
    Debug.Print "Format: " & Range("A1").NumberFormat
    Debug.Print "Value: " & Range("A1").Value
    Debug.Print "Formula: " & Range("A1").Formula
    Range("A1").Formula = "=Sheet2!B:B"
End Sub

And check the Immediate pane to see what's going on.