Looping through cells using VBA

[Ann]

I need to look through a range of cells to see if there is a match to a
variable I have previously set. What is the syntax for referencing this
range? Thanks.

[Tom Ogilvy]

Dim rng as Range, rng1 as Range
Dim myVar as String

myVar = "Amy"
set rng = Range("B9:F100")

set rng1 = rng.find(myVar)
if not rng1 is nothing then
msgbox myvar & " found at " & rng1.Address
Else
msgbox myvar & " was not found in " & rng.Address
End sub

--
Regards,
Tom Ogilvy

"Ann" <Ann@discussions.microsoft.com> wrote in message
news:ABEFB9AF-BF85-4BDC-9290-AF9AA1AFE1B6@microsoft.com...
> I need to look through a range of cells to see if there is a match to a
> variable I have previously set. What is the syntax for referencing this
> range? Thanks.

[Nexus]

For Each c In Range("A1:A10")
If c.Text = "WHATEVER YOUR LOOKING FOR" Then
..... Do what you want here
End If
Next c

In the example change the range to whatever you want and the check
condition.

This way you have multiple check conditions within a case statement and as
each cell is returned you can manipulate each cell very easily.

"Ann" <Ann@discussions.microsoft.com> wrote in message
news:ABEFB9AF-BF85-4BDC-9290-AF9AA1AFE1B6@microsoft.com...
>I need to look through a range of cells to see if there is a match to a
> variable I have previously set. What is the syntax for referencing this
> range? Thanks.