Type mismatch error in Find method

[Guest]

This line:

Set q = .Find(stName, After:=Range("$C$2"))

In the code below throws the error of type mismatch because it does not like
the After:=Range("$C$2"). I have subsequently altered the code as shown
below to make it run properly, but I would appreciate it if someone can
provide an explanation of how the type is mismatched so I can avoid these
goof-ups in the future.


Dim stName As Variant, x As Variant
Worksheets(1).Activate
lr1 = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Row
n = 2
If Worksheets(3).Range("$A$2") = "" Then
With Worksheets(1)
For i = 2 To lr1
If Cells(i, 3) <> .Cells(i, 3).Offset(1, 0) Then
.Cells(i, 3).Copy Worksheets(3).Cells(n, 1)
n = n + 1
End If
Next i
End With
End If
Set SrchRng = Worksheets(3).Range("$A$2:$A" & lr2)
Set FndRng = Worksheets(1).Range("$C$2:$C" & lr1)
For Each c In SrchRng
If Not c Is Nothing Then
stName = c.Value
stRng = c.Address
With Worksheets(1).Range("$C$2:$C" & lr1)
Set q = .Find(stName, After:=Range(stRng).Offset(0, 2)) '
This works
If Not q Is Nothing Then
x = q.Value
Worksheets(3).Range(stRng).Offset(0, 1) =
Application.CountIf(FndRng, x)
End If
End With
End If
Next

 

[Jim Cone]

Try qualifying the range with the correct worksheet...
After:=Worksheet(?).Range("$C$2")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"JLGWhiz" <[email protected]>
wrote in message
This line:
Set q = .Find(stName, After:=Range("$C$2"))

In the code below throws the error of type mismatch because it does not like
the After:=Range("$C$2"). I have subsequently altered the code as shown
below to make it run properly, but I would appreciate it if someone can
provide an explanation of how the type is mismatched so I can avoid these
goof-ups in the future.


Dim stName As Variant, x As Variant
Worksheets(1).Activate
lr1 = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Row
n = 2
If Worksheets(3).Range("$A$2") = "" Then
With Worksheets(1)
For i = 2 To lr1
If Cells(i, 3) <> .Cells(i, 3).Offset(1, 0) Then
.Cells(i, 3).Copy Worksheets(3).Cells(n, 1)
n = n + 1
End If
Next i
End With
End If
Set SrchRng = Worksheets(3).Range("$A$2:$A" & lr2)
Set FndRng = Worksheets(1).Range("$C$2:$C" & lr1)
For Each c In SrchRng
If Not c Is Nothing Then
stName = c.Value
stRng = c.Address
With Worksheets(1).Range("$C$2:$C" & lr1)
Set q = .Find(stName, After:=Range(stRng).Offset(0, 2)) '
This works
If Not q Is Nothing Then
x = q.Value
Worksheets(3).Range(stRng).Offset(0, 1) =
Application.CountIf(FndRng, x)
End If
End With
End If
Next