如何在VBA Excel中使用FIND函数搜索数字？(How to search for a number using FIND function in VBA Excel?)

我正试图找到我的这个问题的解决方案，我尝试使用Find函数来搜索数字。假设我在Sheet1中有以下设置数据并从第1行A列开始：

A B C D E F G No Date Code Name Remarks D e b i t Cr e d i t 1 4/30/2015 004/AB/01/04/15 Anna YES 40239.66 0.00 2 2/16/2015 028/AA/01/02/15 Andy NO 0.00 2205.49 3 1/31/2015 021/DR/04/01/15 Jim YES 167.60 0.00 4 7/14/2015 083/RF/01/07/15 Anna YES 3822.60 0.00 5 8/6/2015 030/AB/01/08/15 Anna NO 0.00 11267.96 6 1/15/2015 020/TY/01/01/15 Barry 0.00 5237.84 7 7/14/2015 024/HU/01/07/15 Anna NO 0.00 3822.60 8 1/31/2015 039/JK/01/01/15 YES 0.00 1780.84 9 1/27/2015 007/ER/01/01/15 Jim NO 5237.84 0.00 10 4/29/2015 077/FX/01/04/15 Barry NO 0.00 40239.66 11 1/3/2015 001/OX/10/01/15 Andy NO 33074.03 0.00 12 8/10/2015 001/PR/01/08/15 Nicholas 11267.96 0.00 13 10/31/2015 007/TX/09/10/15 Jim 1780.84 0.00 14 2/28/2015 071/QR/01/02/15 Andy YES 2205.49 0.00 15 1/7/2015 007/OM/02/01/15 Nicholas 8873.25 0.00

我从以下简单代码开始，在Range("G:G")找到数字40239.66，并在Cells(2, "H")返回匹配数字的行：

Sub Test_1() Dim ValueToFind With Sheets("Sheet1").Range("G:G") ValueToFind = .Cells(2, "F").Value Set FindValue = .Find(what:=ValueToFind, LookIn:=xlValues) If Not FindValue Is Nothing Then FirstValue = FindValue.Row Do .Cells(2, "H") = FindValue.Row Exit Do Loop While Not FindValue Is Nothing And FindValue.Rows <> FirstValue Else .Cells(2, "H") = "Not match" End If End With End Sub

虽然上面的代码没有错误，但是在Cells(2, "H")没有找到任何错误Cells(2, "H") 。如果我用它来查找文本，代码确实有效。我试图通过在线搜索解决问题，但我无法找到明确的答案。我只能找到如何使用Find函数来搜索文本而不是数字。当然，我完成了我能找到的所有建议：清除格式从我想要查找值的范围或更改Lookin:=xlValues to Lookin:=xlFormulas ，但它们没有任何区别。请问有人在这帮帮我吗？提前致谢。

I'm trying to find the solution to this question of mine and I try to employ the Find function to search for a number. Suppose that I have the following set data in Sheet1 and start from row 1 column A:

A B C D E F G No Date Code Name Remarks D e b i t Cr e d i t 1 4/30/2015 004/AB/01/04/15 Anna YES 40239.66 0.00 2 2/16/2015 028/AA/01/02/15 Andy NO 0.00 2205.49 3 1/31/2015 021/DR/04/01/15 Jim YES 167.60 0.00 4 7/14/2015 083/RF/01/07/15 Anna YES 3822.60 0.00 5 8/6/2015 030/AB/01/08/15 Anna NO 0.00 11267.96 6 1/15/2015 020/TY/01/01/15 Barry 0.00 5237.84 7 7/14/2015 024/HU/01/07/15 Anna NO 0.00 3822.60 8 1/31/2015 039/JK/01/01/15 YES 0.00 1780.84 9 1/27/2015 007/ER/01/01/15 Jim NO 5237.84 0.00 10 4/29/2015 077/FX/01/04/15 Barry NO 0.00 40239.66 11 1/3/2015 001/OX/10/01/15 Andy NO 33074.03 0.00 12 8/10/2015 001/PR/01/08/15 Nicholas 11267.96 0.00 13 10/31/2015 007/TX/09/10/15 Jim 1780.84 0.00 14 2/28/2015 071/QR/01/02/15 Andy YES 2205.49 0.00 15 1/7/2015 007/OM/02/01/15 Nicholas 8873.25 0.00

I start with the following simple code to find the number 40239.66 in Range("G:G") and return the row of matched number in Cells(2, "H"):

Sub Test_1() Dim ValueToFind With Sheets("Sheet1").Range("G:G") ValueToFind = .Cells(2, "F").Value Set FindValue = .Find(what:=ValueToFind, LookIn:=xlValues) If Not FindValue Is Nothing Then FirstValue = FindValue.Row Do .Cells(2, "H") = FindValue.Row Exit Do Loop While Not FindValue Is Nothing And FindValue.Rows <> FirstValue Else .Cells(2, "H") = "Not match" End If End With End Sub

Though there's no error in above code, but nothing is found in Cells(2, "H"). The code did work if I used it to find a text. I've tried to fix the issue via online search, but I was not able to find a definitive answer. All I can find are only how to use the Find function to search for a text, not a number. Off course, I worked through all the suggestions that I can find like: cleared the formatting from the range where I want to find the values or changed Lookin:=xlValues to Lookin:=xlFormulas, but they didn't make any differences. Could someone here help me out, please? Thanks in advance.

最满意答案

此代码永远不会在.Cells(2, "F")找到该值，因为赋值位于With块内。您试图索引到列“F”，但.Cells(2, "F")引用的范围是Sheets("Sheet1").Range("G:G") 。该范围内没有列“F”，因此ValueToFind将始终为vbEmpty 。用这个替换那条线......

ValueToFind = Sheet1.Cells(2, "F").Value

......工作得很好。

This code will never find the value in .Cells(2, "F") because the assignment is inside of the With block. You're attempting to index into column "F", but the Range that .Cells(2, "F") refers to is Sheets("Sheet1").Range("G:G"). There isn't a column "F" in that range, so ValueToFind will always be vbEmpty. Replacing that line with this...

ValueToFind = Sheet1.Cells(2, "F").Value

...works just fine.

更多推荐