1

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.

Community
  • 1
  • 1
Anastasiya-Romanova 秀
  • 3,326
  • 28
  • 48
  • 1
    Your `Loop While...` is using Rows instead of Row – Tim Williams Jul 22 '16 at 06:01
  • @TimWilliams Thanks for pointing that out. I fix it but I still have a bit problem in my code. Could you have a look my comment below Comintern's answer and help me out? Thanks... – Anastasiya-Romanova 秀 Jul 22 '16 at 06:42
  • how are you dealing with duplicates? – user3598756 Jul 22 '16 at 09:57
  • @user3598756 That's also my problem. Suppose that I have Debit1, Debit2, Debit3 and Credit1, Credit2, Credit3. So I need the data will be like this Debit1 and Credit1, Debit2 and Credit2, Debit3 and Credit3. If the number is not the same, for example: my data set has Debit1, Debit2 and Credit1, Credit2, Credit3. So I need the data will be like this Debit1 and Credit1, Debit2 and Credit2, Debit2 and Credit3. Could you understand it? – Anastasiya-Romanova 秀 Jul 22 '16 at 10:35
  • you'd better update your question posting a "before" and "after" scenario to let us understand – user3598756 Jul 22 '16 at 10:37
  • @user3598756 I think I'd ask another question since editing the question would be unfair for Comintern. His answer has already answered my question, only a little bit problem regarding the output of my code that I asked in the comment section below his answer. – Anastasiya-Romanova 秀 Jul 22 '16 at 10:44

1 Answers1

1

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.

Comintern
  • 21,855
  • 5
  • 33
  • 80
  • Thanks for the answer. It turned out I made a stupid mistake. But why the data debit with the value of 0 always point at row 2 using my code instead of 3 in [this picture of my real data set](http://i.stack.imgur.com/tr0hh.png)? Other data point at the correct row. – Anastasiya-Romanova 秀 Jul 22 '16 at 05:20
  • I didn't answer your question because I couldn't see your updated code - no sense trying to figure out "old" code. – Tim Williams Jul 22 '16 at 17:47