Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch error in Find method
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch error in Find method
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND type mismatch error, but don't see it | Excel Worksheet Functions | |||
Type mismatch error on Find | Excel Programming | |||
Type Mismatch Error when using InputBox Method | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming |