如何在VBA Excel中使用FIND函数搜索一个数字？ [英] How to search for a number using FIND function in VBA Excel?

问题描述

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

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $姓名备注D ebit Cr编辑
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 / Anna NO 0.00 3822.60
8 1/31/2015 039 / JK / 01/01/15是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吉姆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） 
 
如果Not FindValue没有那么
 FirstValue = FindValue.Row 
 Do 
 .Cells（2，H）= Fi ndValue.Row 
退出Do 
 Loop While Not FindValue Nothing and FindValue.Rows<> FirstValue 
其他
 .Cells（2，H）=不匹配
结束如果
结束
结束小组
  Cells（2，H）中找不到任何错误， code>。如果我用它来找到一个文本的代码确实工作。我试图通过在线搜索来解决这个问题，但我无法找到明确的答案。我只能找到如何使用查找功能来搜索文本，而不是数字。当然，我通过所有的建议，我可以找到像清除：从我想要查找值范围的格式或更改 Lookin：= xlValues 到 Lookin：= xlFormulas ，但是没有任何区别。有人可以帮我吗？提前致谢。
解决方案此代码将 找到 .Cells（2，F）因为赋值在中有块。您试图索引到F列，但是 Range   .Cells（2，F）是指表格（Sheet1）。范围（G：G）。在该范围内没有列F，所以 ValueToFind 将总是为 vbEmpty 。 
 
 pre $ value $ $ c $ Value $ $ $ $ $ $ $ $ $ $  

...工作得很好。

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.

解决方案

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.

这篇关于如何在VBA Excel中使用FIND函数搜索一个数字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！