Building dynamic Ranges

I am trying to create a script that will allow the user to search for a string and then based on the search results it will select all of the cells that found the string inside of it. I figured that a union of ranges would help me with this but I can not figure out what I am doing wrong. Could someone please help. here is the code that I have so far:

<code>
With Rng1
Set Cell = .Find(text)
If Not Cell Is Nothing Then
Set MyRange = Range(Cell.Address)
InitAddress = Cell.Address
End If

Set Cell = .FindNext(Cell)

While Not InitAddress = Cell.Address

If MyRange Is Nothing Then
Set MyRange = Range(Cell.Address)
Else
Set MyRange = Application.Union(MyRange, Range(Cell.Address))
End If
Set Cell = .FindNext(Cell)
Wend
End If
End With
MyRange.CurrentRegion.Select
</code>

Hi engrwanabe

You haven't really told us what the 'problem' actually is. Is your code producing an error or is it just not selecting the cells you want?

If your question is about a "complile error: End If without Block If" - this is because you need to remove the End If on the line after Wend.

If your question is about why it's not selecting all the cells then that's because you are using the Range Object's currentregion property. You must remember that your found cells are likely to be non-continuous so simply MyRange.Select will suffice.

You also need to arrange your code a bit more defensively. If there are no cells containing your search string then this line will throw out an error:

Set Cell = .FindNext(Cell)


I hope that helps.

Colin

Sorry for not explaining very well

The problem that I was having is that the code was selecting all of the cells in the range and not just the cells returned from the Find statement.

I modified the code like you suggested to:
MyRange.Select

Now it is selecting all of the correct cells. Thank you for your help.