Forum Discussion

MelsZey's avatar
MelsZey
Copper Contributor
Oct 28, 2021

Macro For-next Loop Problem Excel

Hello,
I need to pull two-column data from one sheet to another sheet in Excel according to certain rules,
I have three different main determinants.
I wrote a code specific to the first case below, The same code works fine on the previous month's data, but when trying to pull this month's data, it causes a problem.
When I run the following code, the data appears and disappears very quickly.

How can I solve it?

Sub nergis_gunluk_hesaplama_kanal_bazli()
    last = Worksheets("Genel_Ortalama").Range("B" & Rows.Count).End(xlUp).Row
    For j = 4 To last
        If ThisWorkbook.Sheets("Genel_Ortalama").Cells(j, 3).Value = "nergis" Then
            'For m = 4 To 33
                ThisWorkbook.Sheets("Genel_Ortalama").Cells(j, 5).Value = ThisWorkbook.Sheets("EKIM_ORT").Cells(m, 2).Value
                ThisWorkbook.Sheets("Genel_Ortalama").Cells(j, 6).Value = ThisWorkbook.Sheets("EKIM_ORT").Cells(m, 6).Value
            m = m + 1
            'Next j
        End If
    j = j + 2
    Next j
End Sub

 

developer
excel
Excel for web
Formulas and Functions
Macros and VBA
Office Scripts

1 Reply

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Nov 01, 2021
    I don't have an answer to your question, but I do want to share this: It is bad practice to change the value of a loop counter within the loop. I think your loop will do the same if you change
    For j = 4 To last
    to:
    For j = 4 To last Step 3
    and delete the line j = j + 2.

Resources