Issue with Find function against CSV files

Question

I am have a few issues with trying to locate a number that has a length equal to or bigger thans 9 digits long.

I have wrote this and if the number is less than 9 it picks it up otherwise it doesnt.

The numbers are in a format like this 1013617518

Sub LoopThroughDirectory()

Application.DisplayAlerts = False
Dim Ifind As String, rngData As Range
Dim rngFound As Range
Dim r As Long

MyPath = "C:/"
activefile = Dir(MyPath & "*.csv")
r = 1

Start:
If Range("A" & r) = "" Then
Exit Sub
End If

Application.ScreenUpdating = False

Do While activefile <> ""
    Ifind = Range("A" & r).Value
    Workbooks.Open Filename:=MyPath & activefile
    Set rngData = ActiveSheet.Range("B:B")
    Set rngFound = rngData.Find(Ifind, LookIn:=xlValues)

    ActiveWorkbook.Close

    If Not rngFound Is Nothing Then
            Range("B" & r) = activefile
            r = r + 1
            GoTo Start
    Else: Range("B" & r) = "Not Found"
    End If

    activefile = Dir()
    Range("A" & r).Select
Loop

Application.ScreenUpdating = True

r = r + 1
activefile = Dir(MyPath & "*.csv")
GoTo Start

Application.Display Alerts = True

End Sub

Any ideas why it isnt picking them up and pickign up shorter numbers would be great

Answer 1

Change your find statement to

Set rngFound = rngData.Find(Ifind, LookIn:=xlFormulas)

as numbers above 9 digits are usually displayed in xEy notation unless you take additional precautions. This is safe here as you import a CSV which doesn't contain "real" formulas.

hint: Have you simulated your Find by hand (i.e. Ctrl-F) before coding? It doesn't work either, when the CSV is correctly split into rows and you go searching for your 10-digit number "by Value", but it does work "by Formula"

Good luck MikeD