Excel VBA: `range.find()` does not find values which are displayed as `####`

Question

I'm trying to find a specific value in a specific column. For example the value 100000 in the column B. The following code only works if the column is wide enough to display the full number:

Dim rngSearchRange As Range
Set rngSearchRange = ThisWorkbook.Worksheets(1).Columns(2)
Dim searchTerm As Variant
searchTerm = 100000

Dim rngResultRange As Range       
Set rngResultRange = rngSearchRange.Find(What:=searchTerm, lookin:=xlValues, lookat:=xlWhole)

As soon as the column gets to narrow, so Excel only displays ##### instead of 100000 in the specific cell the find-method returns Nothing.

Is there a way to use the find-method based on the actual values and not on the display of the values? If not, are there any alternatives to For Each cell In rng.Cells? Eventually, I'm looing the method which usees up the least resources.

Note: the searchRange is only one column, the searchValue either doesn't exist or only exists once.

Note: there is a followup question on using match() Note: from time to time it seems to work although neither data nor code changes. Unfortunately, I can not reproduce the change. This whole thing might be a bug indeed

Answer 1

Can reproduce the Find failing if the column width is too narrow.

Match doesn't have this problem.

Sub dural()
    Dim rngSearchRange As Range
    Set rngSearchRange = ThisWorkbook.Worksheets(1).Columns(2)
    Dim searchTerm As Variant
    searchTerm = 100000
    Dim rngResultRange As Range

    Dim found As Variant
    found = Application.Match(searchTerm, rngSearchRange, 0)

    If Not IsError(found) Then
        Set rngResultRange = rngSearchRange.Cells(found)
        MsgBox rngResultRange.Address
    End If
End Sub

Depending on your use case, this may be an option, or if not, maybe Range.AutoFit? Though with "I'm trying to find a specific value in a specific column," it sounds like this could be an option.

Answer 2

You could either get the range into an array and loop the array, or just use MATCH:

    Sub test()
        Dim rngSearchRange, rngResultRange As Range
        Dim searchTerm As Variant
        Dim vRow As Variant

        Set rngSearchRange = ThisWorkbook.Worksheets(1).Columns(2)
        searchTerm = 10000
        vRow = Application.Match(searchTerm, rngSearchRange, 0)
        If Not IsError(vRow) Then
            Set rngResultRange = rngSearchRange.Resize(1, 1).Offset(vRow - 1, 0)
        Else
            MsgBox "Not Found"
        End If
End Sub

Answer 3

Try this:

Sub test()
   Dim rngSearchRange, rngResultRange As Range
   Dim searchTerm As Variant

   Set rngSearchRange = ThisWorkbook.Worksheets(1).Columns(2)
   searchTerm = 10000
   Set rngResultRange = rngSearchRange.Find(what:=searchTerm, LookIn:=xlValues)
End Sub

Answer 4

The issue with find is that it only looks for displayed values for some reason, identical to the behaviour of the search box you get pressing crtl+F or clicking the "Find & Select" option on your "Home" ribbon. There is currently no known way to fix this (looking in xlValues and the like as the comments pointed out) As there are various ways to get around this, the (slowest) but most reliable one would be to use a foreach loop as so:

For Each cel In rngSearchRange
If cel.Value = searchTerm Then
Set rngResultRange = cel
exit for '<-If you want the first result, leave this. If you want the last result, omit. Using the first result could be significantly quicker as it will stop looping right away.
End If
Next cel

Just make sure you set your range as definite value like Range("A1:B87") instead of Columns(2) as this will throw a type mismatch error. If you want to search column B, use Range("B:B") instead.

Answer 5

This is a cheating-version: It will copy the range to a temporary Worksheet, converting Formulas to Values, and do the lookup there.

Public Function FindValueInRange(ByVal RangeToSearch As Range, ByVal ValueToFind As Variant) As Range
    Dim WasActive As Worksheet, ScreenUpdating As Boolean, Calculation As XlCalculation
    'Store current position
    Set WasActive = ActiveSheet
    ScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Calculation = Application.Calculation
    Application.Calculation = xlCalculationManual

    'Let's get to work!
    Set FindValueInRange = Nothing 'Default to Nothing
    On Error GoTo FunctionError

    Dim TempSheet As Worksheet, FoundCell As Range, DisplayAlerts As Boolean

    'Create Temp Sheet
    Set TempSheet = Worksheets.Add

    'Copy data to Temp Sheet, in the same location
    TempSheet.Range(RangeToSearch.Address(True, True, xlA1, False)).Value = RangeToSearch.Value

    'Column Width to Maximum!
    TempSheet.Range(RangeToSearch.Address(True, True, xlA1, False)).EntireColumn.ColumnWidth = 255

    'Search the cells in the Temp Sheet
    Set FoundCell = TempSheet.Range(RangeToSearch.Address(True, True, xlA1, False)).Find(ValueToFind, LookIn:=xlFormulas, LookAt:=xlWhole)

    'Return the found cell, but on the original Worksheet
    If Not (FoundCell Is Nothing) Then Set FindValueInRange = RangeToSearch.Worksheet.Range(FoundCell.Address(True, True, xlA1, False))

    'Remove the Temp Sheet
    DisplayAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    TempSheet.Delete
    Application.DisplayAlerts = DisplayAlerts
    Set TempSheet = Nothing

FunctionError:
    On Error GoTo -1 'Reset the error buffer
    'Restore previous position
    WasActive.Activate
    Application.Calculation = Calculation
    Application.ScreenUpdating = ScreenUpdating
End Function

This would then be used like so:

Set rngResultRange = FindValueInRange(rngSearchRange, searchTerm)