vba .find doesn't work

[jackni]

Hi, everyone

on of my record in range("b3") is "Copy of Read Only - Personal Loan 20171231~tmp23409752.xlsx", and this record is written by vba code, it's a workbook name.
when my vba code wants to find this record in range("B:B"), an error occured, it just cann't find this record.
and the following is my code:

If rng.Row = Thisworkbook.sheets("Sheet1").Range("B:B").Find(what:=rng.Offset(0, -2).Value, LookIn:=xlValues, LookAt:=xlWhole).Row Then

I've tryed lookin:=xlformulas too,or even just tryed ".find(what:=rng.Offset(0, -2).Value).row", it sitll doesn't work.
here rng.address is “D3”

The record is in there, but it still cann't find it, it is very strange.
Can someone here can help me with this.
Thank you very much!!!

 

[Trebor76]

Hi jackni,

Not sure why you set the cell address to D3 when the value to be searched is in B3 , but this will do the job nonetheless:

Option Explicit
Sub Macro1()

    Dim rngMyCell  As Range
    Dim rngFound   As Range
    Dim lngLastRow As Long
    
    lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    Set rngMyCell = Range("D3")
    Set rngFound = ThisWorkbook.Sheets("Sheet1").Range("B4:B" & lngLastRow).Find(What:=rngMyCell.Offset(0, -2), LookIn:=xlValues, LookAt:=xlWhole)
    
    'If a match has been found, then...
    If Not rngFound Is Nothing Then
        '...return the found cell address and row number to the Immediate window
        Debug.Print rngFound.Address & vbNewLine & rngFound.Row
    End If

End Sub

Regards,

Robert

 

[jackni]

Hi Trebor76,

Thank you for your reply.
What I mean is my vba code write workbooks name in one file into range("B:B"), and the following code is to find each one record in range("B:B") to do some other work.
when the code find this "Copy of Read Only - Personal Loan 20171231~tmp23409752.xlsx" record, it cann't find.
Well, it should be found, because it is in there, there is no chance that it cann't find.
I've tryed it manually, paste this record in a new workbook, and search it in Find and Replace dialog boxes, it just cann't find!!! Interesting!!! It's impossible!

You can just ingore D3, it is a long story: first, vba write all workbooks name in range B, next, vba find particular sheets in each workbook, so it makes some workbooks record occupy more than one row, next count each record's number,next...

 

[jackni]

I think there has something to do with ~

I searched "Copy of Read Only - Personal Loan 20171231~" and "~tmp23409752.xlsx" in Find and Replace doalog box, it can find.
But when I search "20171231~tmp", it cann't find.
Why?!
Can someone here can help me, Thank you !!!

 

[jackni]

Well, some other record also has ~
the vba code works fine, the record can be found.
but this one cann't found.
I don't know why...

 

[Trebor76]

As Excel uses the tilde (~) key as a "as a marker to indicate that the next character is a literal" (refer here), I think you need to replace it twice in the code string you want to search like so (where cell D3 is what you want to lookup within Col. B of the same tab):

Option Explicit
Sub Macro1()

    Dim rngMyCell  As Range
    Dim rngFound   As Range
    Dim lngLastRow As Long
    
    lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    Set rngMyCell = Range("D3") 'This cell contains the string to be searched in Col. B
    If InStr(rngMyCell, "~") > 0 Then
        Set rngFound = ThisWorkbook.Sheets("Sheet1").Range("B:B").Find(What:=Replace(rngMyCell, "~", "~~"), LookIn:=xlValues, LookAt:=xlWhole)
    Else
        Set rngFound = ThisWorkbook.Sheets("Sheet1").Range("B:B").Find(What:=rngMyCell, LookIn:=xlValues, LookAt:=xlWhole)
    End If
    
    'If a match has been found, then...
    If Not rngFound Is Nothing Then
        '...return the found cell address and row number to the Immediate window
        Debug.Print rngFound.Address & vbNewLine & rngFound.Row
    End If

End Sub

This works for me.

Robert

 

[jackni]

Thank you, Robert.

This does work!!!
Thank you very much for your reference!

 

[Trebor76]

You're welcome. I'm glad we got it sorted