"next" and "for" functions wont work correctly

Question

Sub Hinnan_korotus_välilehti_makro()
    Dim tulostusrivi As Integer
    Dim lukurivi As Integer
    Dim lukurivi_tausta As Integer
    Dim uusihinta As Double

    Sheets("Nimikkeitä").Select

    For lukurivi = 75 To 400
        If Range("F" & lukurivi) = "Hawle" Then
            nimike = Range("A" & lukurivi)
            Sheets("Taul1").Select

            For tulostusrivi = 2 To 400
                If Range("A" & tulostusrivi) = nimike Then
                    uusihinta = Range("F" & tulostusrivi)
                    Sheets("Nimikkeitä").Select
                    Range("E" & lukurivi) = uusihinta
                End If
            Next tulostusrivi
        End If
    Next lukurivi
End Sub

Hi I have a problem with "for" and "next" functions.. Last few lines where macro has placed "uusihinta" to Range "E" & lukurivi. I Want to go back after "End if" to top "lukurivi" and next line which is 76. But it keeps looping "tulostusrivi" and never goes to "Next lukurivi". Thank you!

After `Range("E" & lukurivi) = uusihinta` you need to add `Exit For` on the next line — IAmNerd2000, Feb 03 '19 at 20:24

VBasic2008 · Accepted Answer · 2019-02-04T03:38:06.880

Double Loop

The short answer was already given in the comments by IAmNerd2000 and Eleshar: You are missing the Exit For line. Below is an optimized, over commented version of your code which you might find instructive.

Tips

It is customary not to use underscores "_" in macro titles, but to use capital letters: HinnanKorotusVälilehti, HinKorVäl or HKV instead of Hinnan_korotus_välilehti. And shorter titles are preferred (Makro is really not necessary in the title).
Always use Option Explicit for the compiler to find variables that are not declared (nimike).
Start variables with lower case letters and introduce uppercase letters to divide words e.g. lukuRivi or tulostusRivi.
Long vs Integer
Remove unused variables (lukurivi_tausta).
Do not add unnecessary variables e.g. NOT A = B: C = A but C = B (uusihinta).
Do not use Select or Activate if not necessary, because they slow down your code.
Add variable descriptions or titles for you (after a while) and others to better understand the code.
Use object variables (ws) to make use of Intellisense.
Use the With statement to divide codes of different objects (not quite a demonstration here).
When data (rows) will be added the hard coded last row (400) will always have to be changed. Therefore it is better to calculate it every time using the End property or the Find method.

The Code

Option Explicit

Sub HinnanKorotusVälilehti()

    Dim ws As Worksheet       ' Worksheet Variable for worksheet "Taul1"
    Dim lukuRivi As Long      ' Worksheet "Nimikkeitä" Row Counter
    Dim tulostusRivi As Long  ' Worksheet "Taul1" Row Counter
    Dim nimike As String      ' Product Title

    ' Create a reference to worksheet "Taul1" (Everything starting with
    ' "ws." is referring to workhsheet "Taul1").
    Set ws = ThisWorkbook.Worksheets("Taul1")

    ' In Worksheet "Nimikkeitä" (Everything starting with a "." is referring
    ' to worksheet "Nimikkeitä".)
    With ThisWorkbook.Worksheets("Nimikkeitä")
        ' Loop through rows of worksheet "Nimikkeitä".
        For lukuRivi = 75 To 400
            ' Check if the cell at the intersection of the current row
            ' and column "F" contains "Hawle".
            If .Range("F" & lukuRivi) = "Hawle" Then ' IS "Hawle"
                ' Assign the value of the cell at the intersection of the
                ' current row and column "A" to variable "nimike".
                nimike = .Range("A" & lukuRivi)
                ' (In Worksheet Taul1) Loop through rows of Worksheet Taul1.
                For tulostusRivi = 2 To 400
                    ' Check if the cell at the intersection of the current row
                    ' and column "A" contains (the value of) "nimike".
                    If ws.Range("A" & tulostusRivi) = nimike Then ' IS "nimike".
                        ' Write the value of the cell at the intersection of
                        ' the current row (tulostusRivi) and the "F" column
                        ' in worksheet "Taul1" to the cell at the intersection
                        ' of the current row (lukuRivi) and the "E" column in
                        ' worksheet "Nimikkeitä".
                        .Range("E" & lukuRivi) = ws.Range("F" & tulostusRivi)
                        ' Since the value has been found, stop searching and
                        ' go to next row in worksheet "Nimikkeitä".
                        Exit For
                      'Else ' NOT "nimike".
                    End If
                Next ' Next row in worksheet "Taul1" (tulostusRivi).
              'Else  ' NOT "Hawle".
            End If
        Next ' Next row in worksheet "Nimikkeitä" (lukuRivi).
    End With

End Sub

"next" and "for" functions wont work correctly

1 Answers1

Double Loop

Tips

The Code