CODEWITHSUNDEEP
excelvbaloopscompare
Michael
Michael

Reputation: 15

vba excel compare values

I wonder if somebody could help me complete the vba code. What I want to do is to Compare two values in two different columns (Correct, Compare). If they are equal you should copy a value from third column (Rank) into a fourth column (Output). The "group of Compare" is 39 rowsThe "group of Rank" is 39 rowsThe "group of Correct" is 13 rowsThe "group of Output" is 13 rows So the first loop is Compare (row 2-40), Rank (row 2-40), Correct (row 2-14),Output (row 2-14). Second loop Compare (row 41-79), Rank (row 41-79), Correct (row 15-27),Output (row 15-27) and so on. This code works for the first loop. After that it gets wrong. 

Private Sub CommandButton3_Click()

Dim LastRow
Dim i

LastRow = Cells(Rows.Count, "F").End(xlUp).Row


For i = 2 To LastRow
    Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 6), Columns("D:E"), 2, 0)
Next i

End Sub

Attached below is an image of example data. enter image description here

Upvotes: 0

Views: 321

Answers (2)

Macko
Macko

Reputation: 1

Going by the image you added, wouldn't it be easier to just use VLOOKUP? You will get the same results as the IFS you used.

Try this:

Private Sub CommandButton2_Click()

LastRow = Cells(Rows.Count, "F").End(xlUp).Row

For i = 2 To LastRow
    Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 6), Columns("D:E"), 2, 0)
Next i

End Sub

The LastRow gets the last cell of the column "Correct", so we know how many times the macro will have to go trough the loop. Next we have a For loop which goes (in your example) from 2 till 14. Inside the loop there is a VLOOKUP function that compared value from column Correct with column Compare, and if there is a match it returns value of the column Rank for the 1st match.

Upvotes: 0

user13359193
user13359193

Reputation:

If I understand correctly, you want to copy values from Rank to Output if Compare and Correct are equal. I think all you need in this case is a simple IF statement:

For i = 2 to 26 'I am basing these numbers off the spreadsheet, but you could use 
                 variables here from earlier in your code.

If Cells(i, 4) = Cells(i, 6) Then   'Column 4 and 6 are Compare and Correct, 
                                     respectively.
  Cells(i, 7) = Cells(i, 5)         'Column 7 and 5 are Output and Rank respectively.

Next i

Upvotes: 1

Related Questions