VBA - For ... Next Loop

Question

I'm trying to copy values from one sheet to another, if the conditions are right. I did it with 3 nested loops. Unfortunately the code only gives me the first value.

for example:

i = 7, j = 3, k = 2

(i, j) = (7,3) is an "E" in the "Januar" sheet. This E is then continuously copied and pasted.

But I want the second value (i, j) = (7,4), which is an "U" copied and pasted and so on...

I don't quite understand where the problem is in my code. I would be glad if someone can help me. I would also be happy about a better and faster solution.

Dim i As Integer, Dim j As Integer, Dim k As Integer

For i = 7 To 37
    If Worksheets("Januar").Cells(i, 2).Value = Worksheets("Drucken").Cells(12, 12).Value Then
        For j = 3 To 5
            Worksheets("Januar").Cells(i, j).Copy
                For k = 2 To 4
                    Worksheets("Drucken").Activate
                    Worksheets("Drucken").Cells(38, k).Select
                    ActiveSheet.Paste
                Next
            Worksheets("Januar").Activate
        Next
    End If
Next

EDIT:

Here is what I want

I want these values from Sheet("Januar") copied and pasted into

this Sheet("Drucken")

I solved the problem like this:

Dim i As Integer
   
For i = 7 To 37
If Worksheets("Januar").Cells(i, 2).Value = Worksheets("Drucken").Cells(12, 12).Value Then
        Worksheets("Januar").Cells(i, 3).Copy Worksheets("Drucken").Cells(38, 2)
        Worksheets("Januar").Cells(i, 4).Copy Worksheets("Drucken").Cells(38, 3)
        Worksheets("Januar").Cells(i, 5).Copy Worksheets("Drucken").Cells(38, 4)
        Worksheets("Januar").Cells(i, 6).Copy Worksheets("Drucken").Cells(38, 5)
        Worksheets("Januar").Cells(i, 7).Copy Worksheets("Drucken").Cells(38, 6)
        Worksheets("Januar").Cells(i, 8).Copy Worksheets("Drucken").Cells(38, 7)
        Worksheets("Januar").Cells(i, 9).Copy Worksheets("Drucken").Cells(38, 8)
        Worksheets("Januar").Cells(i, 10).Copy Worksheets("Drucken").Cells(38, 9)
        Worksheets("Januar").Cells(i, 11).Copy Worksheets("Drucken").Cells(38, 10)
        Worksheets("Januar").Cells(i, 12).Copy Worksheets("Drucken").Cells(38, 11)
        Worksheets("Januar").Cells(i, 13).Copy Worksheets("Drucken").Cells(38, 12)
        Worksheets("Januar").Cells(i, 14).Copy Worksheets("Drucken").Cells(38, 13)
        Worksheets("Januar").Cells(i, 15).Copy Worksheets("Drucken").Cells(38, 14)
        Worksheets("Januar").Cells(i, 16).Copy Worksheets("Drucken").Cells(38, 15)
        Worksheets("Januar").Cells(i, 17).Copy Worksheets("Drucken").Cells(38, 16)
        Worksheets("Januar").Cells(i, 18).Copy Worksheets("Drucken").Cells(38, 17)
        Worksheets("Januar").Cells(i, 19).Copy Worksheets("Drucken").Cells(38, 18)
        Worksheets("Januar").Cells(i, 20).Copy Worksheets("Drucken").Cells(38, 19)
        Worksheets("Januar").Cells(i, 21).Copy Worksheets("Drucken").Cells(38, 20)
        Worksheets("Januar").Cells(i, 22).Copy Worksheets("Drucken").Cells(38, 21)
        Worksheets("Januar").Cells(i, 23).Copy Worksheets("Drucken").Cells(38, 22)
        Worksheets("Januar").Cells(i, 24).Copy Worksheets("Drucken").Cells(38, 23)
        Worksheets("Januar").Cells(i, 25).Copy Worksheets("Drucken").Cells(38, 24)
        Worksheets("Januar").Cells(i, 26).Copy Worksheets("Drucken").Cells(38, 25)
        Worksheets("Januar").Cells(i, 27).Copy Worksheets("Drucken").Cells(38, 26)
        Worksheets("Januar").Cells(i, 28).Copy Worksheets("Drucken").Cells(38, 27)
        Worksheets("Januar").Cells(i, 29).Copy Worksheets("Drucken").Cells(38, 28)
        Worksheets("Januar").Cells(i, 30).Copy Worksheets("Drucken").Cells(38, 29)
        Worksheets("Januar").Cells(i, 31).Copy Worksheets("Drucken").Cells(38, 30)
        Worksheets("Januar").Cells(i, 32).Copy Worksheets("Drucken").Cells(38, 31)
        Worksheets("Januar").Cells(i, 33).Copy Worksheets("Drucken").Cells(38, 32)
End If
Next

But I think with for next loops its way better. But I dont know why its not working with the code above...

It would be very helpful if you added a sample of what your source data looks like an what the expected output looks like. I'm thinking that the issue might be that the `For....Next` loops don't work the way you are thinking, but without the sample I can't be sure — Glenn G, Apr 23 '21 at 11:55
Not so clear what you want, in fact. Your code works as you describe it. I mean, it copies three times `Worksheets("Januar").Cells(i, j)` value on the same row (38) in columns 2 to 4. At the second `j` iteration it overwrite the previous returned values and it returns only the last iteration result. Where would you like to copy that values (more then three) for each iteration? Do you like copying on the next empty column of the same 38 row? Do you like incrementing the row, too? What do you want, in fact? — FaneDuru, Apr 23 '21 at 11:58
@FaneDuru.. yes i want to copying on the next column of the same 38 row. — Basara, Apr 23 '21 at 12:08
@Glenn G.. I edited my post. Please have a look. I hope you understand my problem better now. — Basara, Apr 23 '21 at 12:10
Which "these values"? Since we do not know what `Worksheets("Januar").Cells(i, 2).Value = Worksheets("Drucken").Cells(12, 12).Value` does mean, it is not possible to understand what you need. You show us the seventh row and your code processes rows from 7 to 37. What to understand from your answer update? From the second sheet, do you need only three such values to be returned? If not, why do you show us what you do not need? — FaneDuru, Apr 23 '21 at 12:17
@FanDuru sorry. The if statement compares the name in sheet("Drucken") is the same as the name in sheet("Januar"). Worksheets("Drucken").Cells(12, 12).Value Cells(12,12) holds the name of a user — Basara, Apr 23 '21 at 12:20
Your code copies once and pastes three times. You might want to fix the indenting too. Also, you might benefit from reading this: [Some examples of how to avoid select](https://stackoverflow.com/a/10717999/3817068) — Nicholas Hunter, Apr 23 '21 at 12:25
"I want to copy and paste the entire row for i=7" What does that mean? Why do not show us **what you need**? Why do you show us three "E" which looks to not be what you want? It is the last time i ask for clarifications. If I will be able to understand what you need, I will try helping you. If not, I wish you a good day! Then, I cannot understand why I am not notified when you try answering my clarification questions, even if it looks that you tagged me... Can you simply explain **in words**, or in better pictures what and where to be copied? — FaneDuru, Apr 23 '21 at 12:35
Do you need to have `E, UE, E`, instead of `E, E, E` for the first matching case? And then to write starting from the E:E column to G:G and so on? — FaneDuru, Apr 23 '21 at 12:48
@FaneDuru exactly! Sorry its hard for me to explain in english :/ — Basara, Apr 23 '21 at 12:52
Then, please try the code I posted. Still not sure I understood what you need... — FaneDuru, Apr 23 '21 at 12:54

FaneDuru · Accepted Answer · 2021-04-23T13:13:20.130

1

Try the next code, please. I only hope that I could understand what you (really) need:

Sub testIterations()
 Dim i As Long, j As Long, k As Long

 k = 2
 For i = 7 To 37
    If Worksheets("Januar").cells(i, 2).value = Worksheets("Drucken").cells(12, 12).value Then
        For j = 3 To 5
            'Worksheets("Januar").cells(i, j).Copy Worksheets("Drucken").cells(38, k)
            Worksheets("Drucken").cells(38, k).value = Worksheets("Januar").cells(i, j).value
            k = k + 1
        Next
    End If
 Next
End Sub

If my understanding is correct, do you need copying the format, too? Or only the values will be enough. If only the values, the code can be much faster, not involving the clipboard.

edited Apr 23 '21 at 13:13

answered Apr 23 '21 at 12:54

FaneDuru

38,298
4
19
27

Thats the answer I wanted!!!! Thanks a lot mate. Sorry for my bad english... – Basara Apr 23 '21 at 12:59
Only the values would be great! – Basara Apr 23 '21 at 13:04
1

@Basara: OK. I will comment the line using clipboard and put another one. Did it. Please, check it. I did not test the code I posted. No I am leaving my office. If something else, I can work only when I will be at home (in some hours)... – FaneDuru Apr 23 '21 at 13:12
I am driving now. But the code should work. Did you delete the previous vaues/format? – FaneDuru Apr 23 '21 at 13:39

Glenn G · Answer 2 · 2021-04-23T13:06:21.337

Based on the edit to the post: I'd use this code,

Sub Test3()
    Dim i As Long
    Dim j As Long
        For i = 7 To 37
            If Worksheets("Januar").Cells(i, 2) = Worksheets("Drucken").Cells(12, 12) Then
                For j = 1 To 30
                    Worksheets("Drucken").Cells(38, j + 1) = Worksheets("Januar").Cells(i, j + 2)
                Next j
            End If
        Next i
End Sub

Original answer left for informational purposes

The code below will copy your entire row and paste to so the second sheet based on the criteria in xlCellTest using your code above I set it to the same cell as you have but suggest against that cell as it could be overwritten

Sub test2()
    Dim xlCellA As Range
    Dim xlCellB As Range
    Dim xlCellTest As Range
        Set xlCellTest = Worksheets("Drucken").Cells(12, 12)
        Set xlCellA = Worksheets("Januar").Range("B2")
        Set xlCellB = Worksheets("Drucken").Range("A2")
        Do Until xlCellA = ""
            If xlCellA = xlCellTest Then
                xlCellA.EntireRow.Copy
                xlCellB.PasteSpecial xlPasteAll
                Set xlCellB = xlCellB.Offset(1, 0)
            End If
            Set xlCellA = xlCellA.Offset(1, 0)
        Loop
End Sub

VBA - For ... Next Loop

2 Answers2