.Find picking up variable value BUT ALSO *variable value*

[shanep]

Hi

This is a bit of a continuation from the following post:

http://www.mrexcel.com/forum/showthread.php?p=1880080&posted=1#post1880080

but as the subject of the post changed I thought I'd start a new thread.

The Problem
I need to find all instances of a value (e.g. "242") in column U.

I can do this with the following code but it picks up not only "242" but also "x242", "242x" and "x242x".

How can I ensure that it only selects exact matches for "242"???

For lCount = 1 To WorksheetFunction.countIf(Columns("U:U"), vFind)
            'MsgBox vID
            Set rFoundCell = sdWS.Columns("U:U").Find(What:=vID, After:=rFoundCell, LookIn _
            :=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection _
            :=xlNext, MatchCase:=False, SearchFormat:=False)
            'MsgBox rFoundCell.Address
            With rFoundCell
                '.Interior.Color = RGB(255, 45, 67)
                sdWS.Activate
                rFoundCell.EntireRow.Copy
                iTemp = tempWS.Range("B36556").End(xlUp).Row + 1
                tempWS.Activate
                tempWS.Range("A" & iTemp).PasteSpecial Paste:=xlPasteValues
            End With
 
        Next lCount

 

[VoG]

You should really have continued in your original thread but before a moderator locks this one

Set rFoundCell = sdWS.Columns("U:U").Find(What:=vID, After:=rFoundCell, LookIn _
            :=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection _
            :=xlNext, MatchCase:=False, SearchFormat:=False)

 

[shanep]

cheers, and apologies MOD (I thought if the purpose/focus of the thread changed then a new thread was required - i'm now suitably educated )

i updated the code to "LookAt:=xlWhole" but now i get an error highlighting the text in red below. the error is RTE 91: Object variable or With block variable not set...

Dim lCount As Long
        Dim rFoundCell As Range
        Set rFoundCell = Range("U1")
        sdWS.Activate
 
        vFind = "*" & vID & "*"
 
        sdWS.Range("A1:IV1").Copy
        tempWS.Range("A1").PasteSpecial Paste:=xlPasteValues
 
        For lCount = 1 To WorksheetFunction.countIf(Columns("U:U"), vFind)
            'MsgBox vID
            Set rFoundCell = sdWS.Columns("U:U").Find(What:=vID, After:=rFoundCell, LookIn _
            :=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection _
            :=xlNext, MatchCase:=False, SearchFormat:=False)
            'MsgBox rFoundCell.Address
            With rFoundCell
                '.Interior.Color = RGB(255, 45, 67)
                sdWS.Activate
                rFoundCell.EntireRow.Copy
                iTemp = tempWS.Range("B36556").End(xlUp).Row + 1
                tempWS.Activate
                tempWS.Range("A" & iTemp).PasteSpecial Paste:=xlPasteValues
            End With
 
        Next lCount

 

[VoG]

Perhaps

        If Not rFoundCell Is Nothing Then
            With rFoundCell
                '.Interior.Color = RGB(255, 45, 67)
                sdWS.Activate
                rFoundCell.EntireRow.Copy
                iTemp = tempWS.Range("B36556").End(xlUp).Row + 1
                tempWS.Activate
                tempWS.Range("A" & iTemp).PasteSpecial Paste:=xlPasteValues
            End With
        End If

 

[shanep]

ok, i tried that but i got a RTE 13: Type Mismatch on:

Set rFoundCell = sdWS.Columns("U:U").Find(What:=vID, After:=rFoundCell, LookIn _
            :=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection _
            :=xlNext, MatchCase:=False, SearchFormat:=False)

i put a counter in the loop to test how many times it goes through before erroring out and it errors out on the second time through the loop.

also, when i hold the mouse over rFoundCell whilst debugging i get:

rFoundCell = Nothing

...........

 

[VoG]

I may be missing something but I can't understand why that code would error, especially on the second pass through the loop as opposed to the first. I'm not doubting you - just saying that I can't figure it.

I still think that you'd be better off using the FindNext method as I suggested in your previous thread. If nothing else it should be more efficient.

 

[shanep]

i'm coming to the conclusion (probably more out of frustration than knowledge!) that it can't actually be done.

i'll try to hack your FindNext code and let you know how that goes.

cheers for all the help so far!

 

[shanep]

ok, so i hacked your FindNext code and simply had it return the address of any cells it found with the value "242" (of which there are 25 in the worksheet).

unfortunately it only found cells that contained "242" alone (2 in total), and not cells that contained "242" in any format (the other 23).

not sure where to go now. beginning to think that what i want to do isn't possible....

so, just to re-cap (for anyone new to this thread, not Peter) who might be able to help...

i need to search through column U and find any occurrence of "242". the values in the cells are not simply numbers (e.e. 241, 242, 243 etc) but can be any of the following types:

A1.value = "242"
A2.value = "242 - FedEx"
A3.value = "11242"
A4.value = "11242 - Dashboard"
A5.value = "24211"
A6.value = "24211 - Speedy Hire"
A7.value = "24211 - Speedy Hire; 242 - FedEx"
A8.value = "242 - FedEx; 11242 - Dashboard; 24211"

i want to return cells A1, A2, A7 and A8, but not the others (as those cells do not contain exact matches for "242").

thanks

 

[VoG]

In that case (and I'm getting thoroughly confused here) change XlWhole to XlPart .. but then we're back to the beginning

 

[RoryA]

On the second pass through, if rFoundCell is Nothing, then it is invalid to be used as the After:= argument. Since your counter loop uses countif with a wildcard, but your find uses exact matches, you can have errors.