0

My find command can't find records if the casing are not right This is the command I used:

Do Until Cells(i, j) = txttno
    i = i + 1
    If Cells(i, j) = "" Then
    MsgBox ("Record does not exist")
    Exit Sub
    End If

Loop

So, when i want to find of ex: "P-100" i have to type "P-100" it won't work when i type "p-100".

Community
  • 1
  • 1
user3024808
  • 47
  • 2
  • 8
  • Try this `Do Until InStr(1, Cells(i, j), txttno, vbTextCompare)` or `Do Until LCase(Cells(i, j)) = LCase(txttno)` – Siddharth Rout Nov 24 '13 at 11:52
  • I would go with the 2nd option as the first will not do an exact match and can return partial matches as well. – Siddharth Rout Nov 24 '13 at 11:59
  • Also Using `.Find` instead of a `Do-Loop` will be much faster if you have a huge range. Read more about `.Find` [HERE](http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/) – Siddharth Rout Nov 24 '13 at 12:08

1 Answers1

0

The Range.find command saves the settings for each parameter just as the Find dialogue box in Excel does.

VBA help recommends

If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

It is possible that the last time you used Find in either of Excel or VBA you did a case sensitive search and that this setting has persisted into your VBA call.

Mark Fitzgerald
  • 3,048
  • 3
  • 24
  • 29