Define a range of cells in VBA

Hi
I have about 20 cells running across which contains data.

What I would like to do is define the range (ie E12:AI12) in VBA so I can do
a check to see if any of those cells in the range contain a specific word.

Thanks
Noemi

dim myrange as range
set myrange=range("e12:a112")

now you can use <myrange> as range
for e.g
myrange.select

==================

"Noemi" <Noemi@discussions.microsoft.com> wrote in message
news:EE54F52D-B421-4955-A6EC-1A08F1EDBBC9@microsoft.com...
> Hi
> I have about 20 cells running across which contains data.
>
> What I would like to do is define the range (ie E12:AI12) in VBA so I can
do
> a check to see if any of those cells in the range contain a specific word.
>
> Thanks
> Noemi

Hi Noemi,

To find each occurrence, try something like:

'=============>>
Sub Tester()
Dim rng As Range
Dim rCell As Range
Const sStr As String = "fred" '<<==== CHANGE

Set rng = Range("E12:AI12")

For Each rCell In rng.Cells
If Not IsEmpty(rCell.Value) Then
If InStr(1, rCell.Value, sStr, vbTextCompare) Then
'Do something e.g:
MsgBox sStr & " found at " & rCell.Address(0, 0)
Else
'Do something else, or do nothing!
End If
End If
Next

End Sub
'<<=============

---
Regards,
Norman



"Noemi" <Noemi@discussions.microsoft.com> wrote in message
news:EE54F52D-B421-4955-A6EC-1A08F1EDBBC9@microsoft.com...
> Hi
> I have about 20 cells running across which contains data.
>
> What I would like to do is define the range (ie E12:AI12) in VBA so I can
> do
> a check to see if any of those cells in the range contain a specific word.
>
> Thanks
> Noemi