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
– Glenn G, Commented Apr 23, 2021 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
– FaneDuru, Commented Apr 23, 2021 at 11:58
@FaneDuru.. yes i want to copying on the next column of the same 38 row. — Basara
– Basara, Commented Apr 23, 2021 at 12:08
@Glenn G.. I edited my post. Please have a look. I hope you understand my problem better now. — Basara
– Basara, Commented Apr 23, 2021 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
– FaneDuru, Commented Apr 23, 2021 at 12:17

FaneDuru · Accepted Answer · 2021-04-23 13:13:20Z

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, 2021 at 13:13

answered Apr 23, 2021 at 12:54

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

4 Comments

Basara Over a year ago

Thats the answer I wanted!!!! Thanks a lot mate. Sorry for my bad english...

Basara Over a year ago

Only the values would be great!

FaneDuru Over a year ago

@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 Over a year ago

I am driving now. But the code should work. Did you delete the previous vaues/format?

Glenn G · Accepted Answer · 2021-04-23 13:06:21Z

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

Collectives™ on Stack Overflow

VBA - For ... Next Loop

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related