More control over XlFind
I am using a script to let users find the serial number in a column of an excel sheet. However, the problem is search flexibility.
Serial numbers can appear in the following examples: "12345678", "1 345678", "12 345678", as you can see, there are different numbers of spaces after the numbers that cause problems. Also, the first two (or sometimes single) digits indicate the year of the serial number and may or may not be separated by a space.
Is there a way to accommodate these options without having to do multiple searches or adjust the data?
Private Function ExcelFind(r As Excel.Range, s As String)
Dim currentFind As Excel.Range = Nothing
currentFind = r.Find(s, ,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)
If currentFind IsNot Nothing Then
Debug.Print("foundrow " & currentFind.Row)
Debug.Print("foundcol " & currentFind.Column)
Return (currentFind)
Else
Debug.Print("not found (EF1)")
Return Nothing
End If
End Function
source to share
Okay, the array-to-validate approach - it will be much faster than checking per cell as in Maddy Nickam's answer. I shot against A1: AN10000 and it took about 3 seconds. Still not optimal, but more workable than checking the whole cell of the range by cell.
Sub test(r As Excel.Range, s As String)
Dim arr() As Variant
Dim cl as long
Dim rw as long
arr = r.value
cl = 1
Do While cl <= r.Columns.Count
rw = 1
Do While rw <= r.Rows.Count
arr(rw, cl) = Replace(arr(rw, cl), " ", "")
If arr(rw, cl) = s Then
Debug.Print r.Cells(rw, cl).Address
End If
rw = rw + 1
Loop
cl = cl + 1
Loop
End Sub
Edit: I am assuming there r
will be a continuous range with this approach. If this is not the case, you will run into problems with addresses. For example, calling this with Set rng = Union(Range("A1:A3"), Range("B5:B7"))
and passing rng
in r
, only the addresses found Debug.Print
would be A1: A3, whereas Debug.Print(rng.Address)
$ A $ 1 would tell you: $ A $ 3, $ B $ 5: $ B $ 7
source to share