0

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

Improve this question

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Do you mean: That if I replace the comment " ' íf cellvalue in Correct and Compare are equal, copy Rank cell value to the adjacent column (Output)" with your code it will do the thing?
That is correct. Give it a shot. Let me know if it works out.
Try to use a For Loop over a definite range to make sure it is working properly. Then you can go back and change the range using input variables. Using a For Loop should prevent infinite loops from happening.
Also, note that I edited the code i provided. I changed "Next" to "Next i". I had it wrong before.
0

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.

Improve this answer

6 Comments

I am a beginner..Can you please give me the whole code? From Private Sub CommandButton2_Click() to End Sub. Thanks in advance.
Edited the original answer. You dont really need much code to complete the same thing as your IFS formula.
It works for the first 13 rows (2-14, one loop) after that it repeat the same values again. Loop nr two should do the same BUT the Correct value to compare against should be row 15 and the Compare, Rank row should be row 41-79. Loop nr 3 Correct row 28, Compare, Rank row 80-118. I hope that you understand what I mean. Don´t hesitate to write again. I am most grateful for your help so far.
The loop goes only till row 14 because the the LastRow value is 14 (It counts the rows in the Correct column - F . My code will work for as many rows of the Correct column as there are populated when the macro is started. It also looks up the data from whole columns D:E so it also will take into account if you add more data.
The result of the loop is:14 14 33 30 6 6 1 14 14 14 14 14 28 But it should be:15 15 35 34 4 4 1 15 15 15 15 15 30 So something is a litte bit wrong.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.