Loop For to compare the content between two cells and copy data

Question

I have the following VBA code to compare the content (string) between two cells. If it is the same, I must to copy certain cells and paste them in another sheet. However, this code is not working. Please may you advise on how to adjust it?

Dim p As Integer
Dim i As Integer

For i = 12 To RealLastRow
If Worksheets("Pal_clave").Range("V" & i).Value = Worksheets("Pal_clave").Range("V" & i - 1).Value Then

Worksheets("Pal_clave").Range("D" & i).Copy Worksheets("Diagrama").Range("B" & p + 10)

Worksheets("Pal_clave").Range("K" & i).Copy Worksheets("Diagrama").Range("E" & p + 10)

Worksheets("Pal_clave").Range("T" & i).Copy Worksheets("Diagrama").Range("H" & p + 10)

Worksheets("Pal_clave").Range("V" & i).Copy Worksheets("Diagrama").Range("K" & p + 10)

Worksheets("Pal_clave").Range("AB" & i).Copy Worksheets("Diagrama").Range("N" & p + 10)

Worksheets("Pal_clave").Range("AJ" & i).Copy Worksheets("Diagrama").Range("B" & p + 20)

Worksheets("Pal_clave").Range("Y" & i).Copy Worksheets("Diagrama").Range("K" & p + 20)

p = p + 20

End If

Next i

What does "not working" mean? Are you getting an error on a specific line or is it not doing what you expect? — Darrell H, Jun 25 '18 at 15:21
`RealLastRow` - what is that? Is it a function? If so, is the Function present and reachable by the module? — CLR, Jun 25 '18 at 15:25
@CLR RealLasRow is used to identify the last row of the sheet "Pal_clave", like this: RealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row — Carolina Montoya Pachongo, Jun 26 '18 at 16:03

score 1 · Answer 1 · answered Jun 26 '18 at 16:59

It may be that some of your Subroutine is missing, but you do not define RealLastRow. You can shorten your code by setting your long worksheet names as variables and avoiding copy/paste. It is best not to use Select and Activate as used in the previous answer.

Sub LoopFor()

    'Use Long in case there are greater than 32767 rows
    Dim p As Long
    Dim i As Long
    Dim RealLastRow As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet

    'Makes your code shorter
    Set ws1 = ThisWorkbook.Sheets("Pal_clave")
    Set ws2 = ThisWorkbook.Sheets("Diagrama")

    'This will get you the last row, even if there are gaps in the data
    RealLastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

    'You may want to test if there are more than 12 rows
    For i = 12 To RealLastRow
        If ws1.Range("V" & i).Value = ws1.Range("V" & i - 1).Value Then

            'No need to copy/paste
            ws2.Range("B" & p + 10) = ws1.Range("D" & i)
            ws2.Range("E" & p + 10) = ws1.Range("K" & i)
            ws2.Range("H" & p + 10) = ws1.Range("T" & i)
            ws2.Range("K" & p + 10) = ws1.Range("V" & i)
            ws2.Range("N" & p + 10) = ws1.Range("AB" & i)
            ws2.Range("B" & p + 20) = ws1.Range("AJ" & i)
            ws2.Range("K" & p + 20) = ws1.Range("Y" & i)

            p = p + 20

        End If

    Next i

End Sub

Nandan A · Answer 2 · 2018-06-27T08:15:41.860

0

As per my understanding, you have input sheet1 like this and you want compare the cells in A and B columns. If the string matches, copy the particular cell values from sheet1 and paste them into sheet2.

You have to use a For loop and if condition to achieve this.

Try this below code.

Sub CompareAndCopy()

Dim NumberOfValues, i, j As Integer
Dim value1, value2 As String
j = 2

Sheet1.Activate
NumberOfValues = Sheets("Sheet1").Range("A1").End(xlDown).Row

For i = 1 To NumberOfValues

value1 = Range("A" & i).Value
value2 = Range("B" & i).Value

'Comparing the cell values in A and B column
'If value matches then copy and paste them into sheet2 from sheet1

If value1 = value2 Then   
Worksheets("Sheet1").Range("D" & i & ":H" & i).Copy Destination:=Worksheets("Sheet2").Range("A" & j)
Sheet1.Activate
j = j + 1
End If

Next

End Sub

edited Jun 27 '18 at 08:15

answered Jun 25 '18 at 15:39

Nandan A

2,702
1
12
23

Nandan, fair play for having a go but I think you might have made a few too many assumptions here without more info. Also, normally we would steer OP away from using Select / Activate and as they've written code without it (which looks fine) I wouldn't recommend they start using it. – CLR Jun 25 '18 at 15:45
@CLR, Is it good to use Set and Range functions to select cells? – Nandan A Jun 25 '18 at 15:53
That would be far better. Check out https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – CLR Jun 25 '18 at 16:13
Thank you, i will improve it :) – Nandan A Jun 25 '18 at 16:15
@NandanA Thank you. – Carolina Montoya Pachongo Jun 26 '18 at 16:04
@CarolinaMontoyaPachongo, Does my answer fits your question? – Nandan A Jun 26 '18 at 16:22
@NandanA Not really. Unfortunately, the issure is more complex than expected. I had to add another For within the first loop. Thanks anyway! – Carolina Montoya Pachongo Jun 28 '18 at 16:48

Loop For to compare the content between two cells and copy data

2 Answers2