Define a range of cells in VBA

[Guest]

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

 

[R.VENKATARAMAN]

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

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

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

 

[Norman Jones]

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
'<<=============