-1

I have entered in a specific cell via vba this forumla:

=RANK(C3;$C$3:$C$14;0)

But when I manually populate C3 and C3:C14 the cell shows #NAME? until i click on it and press enter, after that the cell shows the correct value resulting from the rank formula.

How can I make the formula working without clicking on it?

Gimmy88
  • 13
  • 5
  • How exactly are you entering this with VBA? Can you share your code? – BigBen Dec 27 '21 at 14:47
  • This is a commonly asked question on SO. Have you tried some of the Internet solutions such as the following: https://answers.microsoft.com/en-us/msoffice/forum/all/formula-is-valid-but-doesnt-work-until-cell-is/974e257c-2fa1-4821-8de0-6a2f8ac95d88 , https://stackoverflow.com/questions/33370783/excel-cells-dont-calculate-until-i-double-click-them – QHarr Dec 27 '21 at 14:57
  • it is inside a loop: `WS1.Cells(i + 2, 4).Formula = "=RANK(C" & i + 2 & ",$C$3:$C$" & NUM + 2 & ",0)"` – Gimmy88 Dec 27 '21 at 14:57
  • 1
    @QHarr thank for sharing, I saw similar topic but none of them solved the issue automatically from vba code – Gimmy88 Dec 27 '21 at 15:03
  • 1
    Side notes: you don't need a loop to do this, you can write a formula with relative references to an entire range, and Excel will update the references down rows and across columns. Secondly, `RANK` is deprecated and one should use `RANK.AVG` or `RANK.EQ`. Thirdly, what language is your Excel installation in? – BigBen Dec 27 '21 at 15:06

1 Answers1

0

As suggested in the above comments you can solve the issue by using standard formula names in ENG.

Gimmy88
  • 13
  • 5