vba .find doesn't work

jackni

Board Regular
Joined
Feb 10, 2011
Messages
73
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
Hi jackni,

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

Code:
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

Board Regular
Joined
Feb 10, 2011
Messages
73
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

Board Regular
Joined
Feb 10, 2011
Messages
73
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

Board Regular
Joined
Feb 10, 2011
Messages
73
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...:banghead:
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
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):

Code:
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
 
Last edited:

jackni

Board Regular
Joined
Feb 10, 2011
Messages
73
Thank you, Robert.

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

Forum statistics

Threads
1,081,530
Messages
5,359,349
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top