Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find exact text match
I am having a problem with the part of a procedure below in that I want it to find an
exact match and it does not do that. For example Cells(n,14) at any point may have SB and the loop is picking up NF-TSB in Rng. I would value any guidance. Set Rng = Range("AA1:AA30") With Rng For N = 15 To 42 Set C = .Find(Cells(N, 14).Value, LookIn:=xlValues) If Not C Is Nothing Then firstAddress = c.Address Do ----STATEMENTS Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If Next End With Kind Regards, Graham |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find exact text match
How about this one
Dim k As Variant Set Rng = Range("AA1:AA30") For N = 15 To 42 k = Application.Match(Cells(N, 14), Rng, 0) If Not IsError(k) Then '----STATEMENTS End If Next keiji "Graham" wrote in message ... I am having a problem with the part of a procedure below in that I want it to find an exact match and it does not do that. For example Cells(n,14) at any point may have SB and the loop is picking up NF-TSB in Rng. I would value any guidance. Set Rng = Range("AA1:AA30") With Rng For N = 15 To 42 Set C = .Find(Cells(N, 14).Value, LookIn:=xlValues) If Not C Is Nothing Then firstAddress = c.Address Do ----STATEMENTS Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If Next End With Kind Regards, Graham |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find exact text match
Always best to include the Find function's optional arguments, which can be
left in an unknown state by previous manual use of Find. change Set C = .Find(Cells(N, 14).Value, LookIn:=xlValues) to Set c = .Find(Cells(N, 2).Value, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) I want it to find an exact match use LookAt:=xlWhole If you want case sensitive change MatchCase:=False to MatchCase:=True The example is suitable for use in XL 97 & 2000, it's probably OK to omit other arguments introduced in later versions. Regards, Peter T "Graham" wrote in message ... I am having a problem with the part of a procedure below in that I want it to find an exact match and it does not do that. For example Cells(n,14) at any point may have SB and the loop is picking up NF-TSB in Rng. I would value any guidance. Set Rng = Range("AA1:AA30") With Rng For N = 15 To 42 Set C = .Find(Cells(N, 14).Value, LookIn:=xlValues) If Not C Is Nothing Then firstAddress = c.Address Do ----STATEMENTS Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If Next End With Kind Regards, Graham |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find exact text match
See if this works right...
Set Rng = Range("AA1:AA30") With Rng For N = 15 To 42 Set c = .Find(Cells(N, 14).Value, LookIn:=xlValues, LookAt:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do '----STATEMENTS Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If Next End With Mark "Graham" wrote in message ... I am having a problem with the part of a procedure below in that I want it to find an exact match and it does not do that. For example Cells(n,14) at any point may have SB and the loop is picking up NF-TSB in Rng. I would value any guidance. Set Rng = Range("AA1:AA30") With Rng For N = 15 To 42 Set C = .Find(Cells(N, 14).Value, LookIn:=xlValues) If Not C Is Nothing Then firstAddress = c.Address Do ----STATEMENTS Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If Next End With Kind Regards, Graham |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find exact text match
Thanks to all for the replies, I have now managed to get the procedure to work fine thanks
to your efforts. I am very grateful for all your help. Graham Graham wrote: I am having a problem with the part of a procedure below in that I want it to find an exact match and it does not do that. For example Cells(n,14) at any point may have SB and the loop is picking up NF-TSB in Rng. I would value any guidance. Set Rng = Range("AA1:AA30") With Rng For N = 15 To 42 Set C = .Find(Cells(N, 14).Value, LookIn:=xlValues) If Not C Is Nothing Then firstAddress = c.Address Do ----STATEMENTS Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If Next End With Kind Regards, Graham |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
Find exact match. | Excel Programming | |||
Find a not exact match using vlookup | Excel Discussion (Misc queries) | |||
find (exact match) ? | Excel Programming | |||
Find a match that;s not exact | Excel Worksheet Functions |