EXCEL VBA: looping through columns and copying

Question

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

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. — arcadeprecinct, Aug 26 '16 at 11:35
You don't need VBA for that. Using an appropriate formula in the second column will suffice. Search for VLOOKUP() and IFERROR() — iDevlop, Aug 26 '16 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 — Anastasiya-Romanova 秀, Aug 26 '16 at 11:55

Ralph · Answer 1 · 2016-08-26T12:42:29.307

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:

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

Just delete the code segment you don't want.

edited Aug 26 '16 at 12:42

answered Aug 26 '16 at 11:56

Ralph

9,284
4
32
42

Thanks. This gives a compile error though. It says Invalid or unqualified reference. Any idea why that could be? – kit Aug 26 '16 at 12:10
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. – Ralph Aug 26 '16 at 12:23
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). – Ralph Aug 26 '16 at 12:26
This removes values from the fourth column but does not copy & paste them to the 2nd column. – kit Aug 26 '16 at 12:39
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. – Ralph Aug 26 '16 at 12:40

Anastasiya-Romanova 秀 · Answer 2 · 2016-08-26T12:49:02.257

0

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.

edited Aug 26 '16 at 12:49

answered Aug 26 '16 at 12:07

Anastasiya-Romanova 秀

3,326
28
48

Thanks. It says Compile error: Sub or Function not defined – kit Aug 26 '16 at 12:36
@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. – Anastasiya-Romanova 秀 Aug 26 '16 at 14:37
@Ralph No need for that. I've already forgotten this thing. No hurt feeling for sure. Thanks for the gift. I appreciate it. :) – Anastasiya-Romanova 秀 Aug 28 '16 at 09:29

EXCEL VBA: looping through columns and copying

2 Answers2