Find method has issues with small width columns?

Question

I was working in a Function that gives me the Column in wich it finds one value. I was having trouble getting it done...But I made it work!!

The ERROR, believe it or not, was that the Find method has issues with finding values in cells which width is too small... Could that be so dumb?

This is the call..

Private Sub CommandButton3_Click()

 Direccion = BuscarCol(2)
 MsgBox "the cell address is " & Direccion

End Sub

And this is the function...

Function BuscarCol(Fecha As Integer) As String
Dim RangoFech As Range
With Sheets("REGISTRO").Range("A1:IN1")
Set RangoFech = .Find(What:=Fecha, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False)
    If Not RangoFech Is Nothing Then
        BuscarCol = ConvertToLetter(RangoFech.Column)

End If
End With
End Function

Oh, and I have one more for converting Column numbers to letters, but this never was the problem..

Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
  ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
  ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function

So...can you tell me if that is right? The Find method has that problem?

Side note, your `ConvertToLetter` function fails for some values, eg try 53, 79, 80 ( [see this answer](http://stackoverflow.com/a/12797190/445425) for a robust solution) — chris neilsen, Oct 04 '16 at 03:27

score 5 · Answer 1 · answered Oct 03 '16 at 22:34

If you choose Look in: Values in the Find box, it will only find values that are visible. If the column is hidden, it won't find it. If the column is too narrow to display the value and it displays ### instead, it won't find it. Even if you format the cell as ;;; to effectively hide the value, it won't find it.

If you choose Look in: Formulas, it will find it in all those cases.

But if you have a formula that produces a value, and that value doesn't exist in the text of the formula, it won't find it. So =200+22 is in a cell and you search for 222

In Values: Only will find it if it's visible
In Formulas: Will not find it

It seems like an odd design decision. My theory is that the algorithm uses the Range.Text property. That property returns what is visible in the cells, such as ### for narrow columns, rather than .Value or .Value2.

Find method has issues with small width columns?

1 Answers1