Tap F9 until value is true..
 

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.

Tap F9 until value is true..
 

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


Kashyap wrote:

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.

--

Dave Peterson

Tap F9 until value is true..
 

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

Dave Peterson wrote:

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

Kashyap wrote:

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.

--

Dave Peterson

--

Dave Peterson

Tap F9 until value is true..
 

Thnaks Dave

"Dave Peterson" wrote:

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

Dave Peterson wrote:

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

Kashyap wrote:

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.

--

Dave Peterson

--

Dave Peterson