CODEWITHSUNDEEP
vbaexcel
Michael J
Michael J

Reputation: 33

Search multiple values. If found - perform action. If not - get to next value

Sub finddataalfa1()

Dim athletename As String
Dim finalrow_A As Integer
Dim finalrow_D As Integer
Dim i As Integer
Dim j As Integer

finalrow_A = Sheets("db1").Cells(Rows.Count, 1).End(xlUp).Row
finalrow_D = Sheets("db1").Cells(Rows.Count, 4).End(xlUp).Row

For i = 1 To finalrow_D
    athletename = Sheets("db1").Cells(i, 4).Value

    For j = 1 To finalrow_A
        If Cells(j, 1) = athletename Then 'if match between lets say D1 and A1
            Cells(j, 5) = Cells(j, 2).Value 'copy B1 value to E1 cell
        End If
    Next j
Next i

End Sub

This code works fine. It takes value from D1- finds it in column A. If found in, lets say A10 cell, the script takes value of B10 and copies it to E10 cell. But there is a bug:

 A            B    C        D         E
AB0023999   3999        AB0023999   3999
AB0024000   4000        AB0024000   4000
AB0024001   4001        AB0024001   4001
AB0024002   4002        5000000 
AB0024003   4003        AB0024003   4003
AB0024004   4000        AB0024004   4000
AB0024005   4005        AB0024005   4005
AB0024006   3999        AB0024006   3999
AB0023999   3999        56666       3999
AB0024000   4000        56666       4000
AB0024001   4001        56667       4001
AB0024002   4002        56668   
AB0024003   4003        56669       4003
AB0024004   4000        56670       4000
AB0024005   4005        56671       4005
AB0024006   3999        56672       3999

As you see D1 = A1, so it takes B1 and copies to E1 and goes to D2... unless it gets to value 56666 why it matches to A column... I don't understand. And where does the value 3999 gets from.. Will appreciate your help! Thanks in advance!

Upvotes: 0

Views: 50

Answers (1)

Pavel_V
Pavel_V

Reputation: 1230

you are probably forgetting that the macro does not stop when it finds the first match. Lets take your first number in D column - AB0023999. It starts looping through A column, immediately finds a match on first line. So it copies B to E, but continues on in A column until it finds AB0023999 again in line 9, so it copies B to E again. If you want to copy from B only when the numbers in columns A and E equal in the same row, then you could simply use formula

=IF(D2=A2;B2;"")

Upvotes: 1

Related Questions