Tap F9 until value is true..

  • Thread starter Thread starter Kashyap
  • Start date Start date
K

Kashyap

Hi,

I have a formula in N1 that indicates if M:M don't have any duplicates. If
there are no duplicates it says 'True' else 'false'

Need to macro to run until value in N1 becomes true..

Thanks.
 
Maybe something like:

Option Explicit
Sub testme()
Dim myCell As Range
Dim MaxCount As Long
Dim iCtr As Long
Dim FoundIt As Boolean
Dim wks As Worksheet

Set wks = ActiveSheet

Set myCell = wks.Range("N1")

MaxCount = 1000 'no more than 1000 for my testing

FoundIt = False
If myCell.Value = True Then
'do nothing
FoundIt = True
Else
iCtr = 0
Application.ScreenUpdating = False
Do
iCtr = iCtr + 1
Application.Calculate
If myCell.Value = True Then
FoundIt = True
Exit Do
Else
If iCtr > MaxCount Then
Exit Do
End If
End If
Loop
Application.ScreenUpdating = True
End If

If FoundIt = False Then
MsgBox "No solution found after: " & MaxCount & " tries."
Else
MsgBox "ok--found on iteration: " & iCtr
End If

End Sub
 
I really wanted the "ictr = 0" higher up in the code (I changed something else
and didn't fix that line):

Option Explicit
Sub testme()

Dim myCell As Range
Dim MaxCount As Long
Dim iCtr As Long
Dim FoundIt As Boolean
Dim wks As Worksheet

Set wks = ActiveSheet

Set myCell = wks.Range("N1")

MaxCount = 1000 'no more than 1000 for my testing

iCtr = 0
FoundIt = False
If myCell.Value = True Then
'do nothing
FoundIt = True
Else
Application.ScreenUpdating = False
Do
iCtr = iCtr + 1
Application.Calculate
If myCell.Value = True Then
FoundIt = True
Exit Do
Else
If iCtr > MaxCount Then
Exit Do
End If
End If
Loop
Application.ScreenUpdating = True
End If

If FoundIt = False Then
MsgBox "No solution found after: " & MaxCount & " tries."
Else
MsgBox "ok--found on iteration: " & iCtr
End If

End Sub
 
Thnaks Dave

Dave Peterson said:
I really wanted the "ictr = 0" higher up in the code (I changed something else
and didn't fix that line):

Option Explicit
Sub testme()

Dim myCell As Range
Dim MaxCount As Long
Dim iCtr As Long
Dim FoundIt As Boolean
Dim wks As Worksheet

Set wks = ActiveSheet

Set myCell = wks.Range("N1")

MaxCount = 1000 'no more than 1000 for my testing

iCtr = 0
FoundIt = False
If myCell.Value = True Then
'do nothing
FoundIt = True
Else
Application.ScreenUpdating = False
Do
iCtr = iCtr + 1
Application.Calculate
If myCell.Value = True Then
FoundIt = True
Exit Do
Else
If iCtr > MaxCount Then
Exit Do
End If
End If
Loop
Application.ScreenUpdating = True
End If

If FoundIt = False Then
MsgBox "No solution found after: " & MaxCount & " tries."
Else
MsgBox "ok--found on iteration: " & iCtr
End If

End Sub
Click to expand...
 
Back
Top