Strange behaviour using Range().Find and column width

[Tom_H]

If I run the code below it works fine. First test1 and then test2.
As long as the width of the columns show the whole number in the cells!!
But if I make column A as narrow as the number change to ## and hide column B, test2 will not find the first and the second number?!
(I found a workaround using "match", not tested yet..)

Sub test1()
    Cells(1, 1) = 12345
    Cells(1, 2) = 23456
    Cells(1, 3) = 34567
    
    Dim rng As Range
    Set rng = Range(Cells(1, 1), Cells(1, 3))
    rng.Copy Destination:=Cells(1, 5)
End Sub


Sub test2()
    Dim rngFind As Range
    
    For c = 5 To 7
        Set rngFind = Range(Cells(1, 1), Cells(1, 3)).Find(What:=Cells(1, c), LookIn:=xlValues, LookAt:=xlWhole)
        If rngFind Is Nothing Then
            MsgBox "Cant find " & Cells(1, c)
        Else
            MsgBox "Found " & rngFind
        End If
    Next c
End Sub

 

[Tom_H]

if I change to text it works fine...

    Cells(1, 1) = "asdfg"    
    Cells(1, 2) = "sdfgh"
    Cells(1, 3) = "dfghj"

 

[vmjan02]

hi,

Have implement you code to a command button and it is working all fine for me. after change the column with of A till ## and hide column B.
Just a suggestion, check of the column B format cell Locked or hidden.
Could be possible that your other codes could protect the column B after hide.

Private Sub CommandButton1_Click()
'Sub test1()
Cells(1, 1) = 12345
Cells(1, 2) = 23456
Cells(1, 3) = 34567

Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(1, 3))
rng.Copy Destination:=Cells(1, 5)
End Sub


Sub test2()
Dim rngFind As Range

For c = 5 To 7
Set rngFind = Range(Cells(1, 1), Cells(1, 3)).Find(What:=Cells(1, c), LookIn:=xlValues, LookAt:=xlWhole)
If rngFind Is Nothing Then
MsgBox "Cant find " & Cells(1, c)
Else
MsgBox "Found " & rngFind
End If
Next c
End Sub

 

[Tom_H]

OK, found a better solution...
Change LookIn:=xlValues to LookIn:=xlFormulas