1

In excel I have four columns. There are numbers in the first column, the second column is blank, the third also contains numbers and the fourth contains text.

I want to check each value in the first column and check if it exists in the third column. If it does the value in the fourth column next to the corresponding third column should be copied up to the second column next to the corresponding first column.

I am getting the error compile error. Next without For. Here is my code so far:

Sub Compare() Dim colA As Integer, colB As Integer colA = Columns("A:A").Rows.Count colB = Columns("C:C").Rows.Count For I = 2 To colA 'loop through column A For j = 2 To colB 'loop through column C ' If a match is found: If Worksheets("Sheet1").Cells(I, 1) = Workshee("Sheet1").Cells(j, 3) Then ' Copy Worksheets("Sheet1").Cells(j, 4) = Worksheets("Sheet1").Cells(I, 2) 'Exit For Next j Next I End Sub
Improve this question
3
  • 1
    you are not closing your If. Add End If before Next j. Btw, a quick google search of "Next without For" would have given the same answer. Commented Aug 26, 2016 at 11:35
  • 1
    You don't need VBA for that. Using an appropriate formula in the second column will suffice. Search for VLOOKUP() and IFERROR() Commented Aug 26, 2016 at 11:42
  • You're looping entire rows in Column A and B, yet you set their values as Integer. Try to declare them as Long. Use also a proper LastRow statement Commented Aug 26, 2016 at 11:55

2 Answers 2

Reset to default
2

As already pointed out in the comments above you could also accomplish this with a VLookUp or a combination of INDEX/MATCH. Yet, if you wish to stick with VBA then you should adjust your code a bit.

Option Explicit Sub Compare() Dim ws As Worksheet Dim i As Long, j As Long Dim colA As Long, colC As Long Set ws = ThisWorkbook.Worksheets("Sheet1") colA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row colC = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row 'loop through column A For i = 2 To colA 'loop through column C For j = 2 To colC ' If a match is found: If ws.Cells(i, 1).Value2 = ws.Cells(j, 3).Value2 Then ' Copy column B to Column D as written in your code above ws.Cells(j, 4).Value2 = ws.Cells(i, 2).Value2 ' or copy column D to Column B as written in the question / post ws.Cells(i, 2).Value2 = ws.Cells(j, 4).Value2 'Exit For End If Next j Next i ws.Range("D2:D" & colC).FormulaR1C1 = "=INDEX(R2C2:R" & colA & "C2,MATCH(RC[-1],R2C1:R" & colA & "C1,0))" End Sub

The above code will do both:

  1. the VBA way and
  2. write the INDEX/MATCH formulas for you.

Just delete the code segment you don't want.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks. This gives a compile error though. It says Invalid or unqualified reference. Any idea why that could be?
Sorry, my mistake. I started to code with a With statement and then changed it for a Set ws = ThisWorkbook.Worksheets("Sheet1"). So, there were a few methods still starting with a . instead of ws.. I changed that. Now it should work.
Note, that there is a difference between the VBA solution and the solution with the formula if more than one match is found. The formula will show the first match while the VBA solution will give you the last match (as it overwrites the value in D each time that a match is found).
This removes values from the fourth column but does not copy & paste them to the 2nd column.
Correct. Just like your initial code above. Yet, if you with to copy column D to column B then you'll have to adjust the code a bit (from your original post). Both variations are now in the adjusted solution posted above.
-1

If you insist on using your code, then use this fixed version. It should work fine though it's untested.

Sub Compare() Dim LastRowA As Long, LastRowB As Long, i As Long, j As Long With Worksheets("Sheet1") LastRowA = .Range("A" & Rows.Count).End(xlUp).Row LastRowC = .Range("C" & Rows.Count).End(xlUp).Row For i = 2 To LastRowA For j = 2 To LastRowC If .Cells(i, 1) = .Cells(j, 3) Then .Cells(i, 2) = .Cells(j, 4): Exit For Next j Next i End With End Sub

Let me know in the comment section if there's any error.

Improve this answer

3 Comments

Thanks. It says Compile error: Sub or Function not defined
@Ralph No, I didn't do such a thing. I've never done it & I never will. See my comment below the OP. If you think I copied yours, I'll delete this answer altogether. The truth is I copied the OP's code & edited accordingly. It just happened you posted the answer first while I was writing this answer. And I regret answering this question in the first place. Thanks for the advice, though.
@Ralph No need for that. I've already forgotten this thing. No hurt feeling for sure. Thanks for the gift. I appreciate it. :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.