Intersect Function Problem

[kathy.aubin]

I'm trying to run this function but I'm getting an error witht the
Intersect function for my second worksheet. Any idea why?!

Function SelectRangeToChange(X As Range, ToFind1 As String, ToFind2 As
String, TabNum As Variant) As Range
Dim Selection As Range, Cell As Range, ToChange1 As Range,
ToChange2 As Range, ToChange3 As Range
For i = 1 To TabNum Step 1
Worksheets(i).Select
Set Selection = Intersect(X, ActiveSheet.UsedRange)
***************PROBLEM******
For Each Cell In Selection
If (((Cell.Value) = ToFind1) Or (InStr(1, Cell.Value,
ToFind1, vbTextCompare))) Then
If ToChange1 Is Nothing Then
Set ToChange1 = Cell
Else: Set ToChange1 = Union(ToChange1, Cell)
End If
ElseIf (((Cell.Value) = ToFind2) Or (InStr(1, Cell.Value,
ToFind2, vbTextCompare))) Then
If ToChange2 Is Nothing Then
Set ToChange2 = Cell
Else: Set ToChange2 = Union(ToChange2, Cell)
End If
Else
If ToChange3 Is Nothing Then
Set ToChange3 = Cell
Else: Set ToChange3 = Union(ToChange3, Cell)
End If
End If
Next Cell
Next i
End Function

Thanks,

 

[Guest]

For something like:
Intersect(X, ActiveSheet.UsedRange).Select may be Nothing!

 

[Guest]

first I wouldn't define selection as a variable.

It is already defined for another purpose in Excel/VBA.

second, If you try to do intersect on ranges on different sheets, you will
get an error.
? intersect(worksheets(2).Columns(1),worksheets(2).Rows(1)) is nothing
False
' works, but changing to check two different sheets
? intersect(worksheets(1).Columns(1),worksheets(2).Rows(1)) is nothing

raises an error.

--
Regards,
Tom Ogilvy

 

[Die_Another_Day]

Kathy, from what I can tell you are passing a range to this function,
then changing sheets and asking for an intersect. From what I
understand from Tom, Range is an object and must have a parent object,
which is the Sheet on which the Range resides, so in affect, you are
asking for the Intersection of the original sheet, and the second sheet
which does not exist. Perhaps you could look for the intersection of
Range(x.Address).

HTH

Charles Chickering

 

[kathy.aubin]

Thanks guys!!

 

[Guest]

This at least works - don't know how what you are really doing.

Function SelectRangeToChange(X As Range, _
ToFind1 As String, ToFind2 As String, _
TabNum As Variant) As Range
Dim Cell As Range, ToChange1 As Range
Dim ToChange2 As Range, ToChange3 As Range
Dim rng As Range
Set sh = X.Parent
Set rng = Intersect(X, sh.UsedRange)
For Each Cell In rng
If InStr(1, Cell.Value, ToFind1, vbTextCompare) Then
If ToChange1 Is Nothing Then
Set ToChange1 = Cell
Else
Set ToChange1 = Union(ToChange1, Cell)
End If
ElseIf InStr(1, Cell.Value, ToFind2, vbTextCompare) Then
If ToChange2 Is Nothing Then
Set ToChange2 = Cell
Else
Set ToChange2 = Union(ToChange2, Cell)
End If
Else
If ToChange3 Is Nothing Then
Set ToChange3 = Cell
Else: Set ToChange3 = Union(ToChange3, Cell)
End If
End If
Next Cell
' Set SelectRangeToChange = What? what do you want to return
' dummy line to display results in immediate window
Debug.Print ToChange1.Address
Debug.Print ToChange2.Address
Debug.Print ToChange3.Address
End Function

' used to test the above

Sub Tester1()
SelectRangeToChange Worksheets("Sheet1"). _
Range("A:E"), "A", "B", 5
End Sub

-' produced
$A$1,$C$5,$D$6:$D$8,$C$9:$D$9,$D$10,$D$12,$C$14
$B$1,$A$2,$E$12
$C$1:$E$1,$B$2:$E$2,$A$3:$E$4,$D$5:$E$5,$C$6:$C$8,$E$6:$E$10,$D$11:$E$11,$C$10:$C$13,$A$5:$B$14,$D$13:$E$14