Formulas not refreshing second time button is clicked.

A button is clicked to refresh data.  It works the 1st time but not the second.  Is there anyone that can look at this code and tell me why it would refresh one time and not the other?  It seems to refresh when the sheet is first open and initiated but not the second time.

Dim iD As Integer, iK As Integer
Dim EKN As String

EKNFormula = False
Application.EnableEvents = True
iK = 1
If Sheet1.CommandButton3.Caption = "Refresh" Then
    iD = Range("J6").End(xlDown).Row
    EKN = "=RData(J6:J" & iD & ",K4,,," & iQt & "CH:Fd" & iQt & ",K5)"
    Sheet1.Range("J4").Formula = EKN
    iD = iRefresh
    Sheet1.CommandButton3.Caption = "Lock Formulas"
    Sheet1.CommandButton3.Caption = "Refresh"
   End If

EKNFormula = True

End Function
Public Function iRefresh() As Integer

iRefresh = 0
While iRefresh < 55
    On Error Resume Next
     iRefresh = iRefresh + 1
iRefresh = 1

End Function
Who is Participating?
Robberbaron (robr)Connect With a Mentor Commented:
not sure.

the Irefresh simply does a DoEvents 55 times.  Is there a reason for 55 times ?
if you are expecting an externla update or similar, you would be better waiting a few seconds.

have you turned sheet update off somewhere after this code, then dont turn it on again ?
that would explain why the initial calc works, and the first 'refresh' does.
Application.ScreenUpdating = True

Open in new window

instead of
iRefresh = 1

Open in new window

as the later is not useful anyway.
leezacAuthor Commented:
Thanks - that is helpful - will try
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.