Looping a Macro

[Guest]

Hello. I have created a very simple macro within Excel. It calculates the
sheet (F9) , then searches for a value ( F4 ) . It works, but I need it to
repeat this action continuously until it doe's NOT find the searched for
value. I am using Excel 2003. Thank you. Here is my code :

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/21/2007 by
'
' Keyboard Shortcut: Ctrl+z
'
ActiveSheet.Calculate
Selection.FindNext(After:=ActiveCell).Activate
End Sub

 

[Gary Keramidas]

look at findnext in vba help

 

[Guest]

I have no knowledge of programming or it's terms and phrases. I am hoping
that someone will advance me the correct code to make this macro repeat
itself. I had already looked in help but was not able to reach my goal.
Thanks.

 

[Gord Dibben]

What do you want to do with the found cells?

This macro will color them.

Sub findthings()
whatsit = ActiveSheet.Range("F4").Value
With ActiveSheet.UsedRange
Set c = .Find(whatsit, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

I have a column of cells that use the RANDBETWEEN function. Previously, I was
using the old Windows Recorder program with Excel to find a specific value
within the column-- "F9" to create a fresh batch of random numbers, then
"Shift F4" to find the specific value. I set the Recorder to "Continuous
Loop" so that it would repeat this hundreds of times until the searched for
value was NOT found.
The macro would stop with an error message, exactly what I wanted, as I have
a formula cell that counts the number of calculations that it required to
reach this state. I want an Excel macro to do this exact same thing. Is this
possible? Windows Recorder works for a while but has far, far too many errors
and stops to be practical in use. The purpose behind all this is for
probability research. Thank you.