For loop stop on blank

[Weeble]

So i've written a code to go thrue my drop down list and print every sheet.
I wan't to take it to the next level and do a For loop to do this for me.

Could anyone help me modify my code so that the loop goes thrue all the values but if it hits a "" space, stops.

Sub NastaButik()    Dim v As Variant
    With Sheets("Utskrift").Range("D2")
        If .Value = "" Then
            .Value = Sheets("Butiker").Range("D2").Value
        Else
            v = Application.Match(.Value, Sheets("Butiker").Range("D2:D75"), 0)
            If IsNumeric(v) Then
                .Value = Sheets("Butiker").Range("D2:D75").Cells(v + 1, 1).Value
                If Sheets("Utskrift").Range("K2") > 0 Then
                   ' ActiveSheet.PrintOut
                   MsgBox ("Printed")
                End If
            Else
                .Value = ""
            End If
        End If
    End With
End Sub

 

[nemmi69]

You could try

Sub NastaButik()    Dim v As Variant
    With Sheets("Utskrift").Range("D2")
        If .Value = "" Then
            .Value = Sheets("Butiker").Range("D2").Value
        Else
            v = Application.Match(.Value, Sheets("Butiker").Range("D2:D75"), 0)
            If IsNumeric(v) Then
                .Value = Sheets("Butiker").Range("D2:D75").Cells(v + 1, 1).Value
            If Trim(.Value) ="" then Exit Sub
                 If Sheets("Utskrift").Range("K2") > 0 Then
                   ' ActiveSheet.PrintOut
                   MsgBox ("Printed")
                End If
            Else
                .Value = ""
            End If
        End If
    End With [COLOR=#333333]End Sub
[/COLOR]

 

[Weeble]

Thank you Nemmi, this probably fixes stop issue, but not it looping thrue the list

 

[nemmi69]

Bit confused as to what the steps are in this. Can you explain them?

 

[Johnny C]

Assuming utskrift is a sheet you want to vary, something along these lines

Sub NastaButik()    
Dim v As Variant
Dim sht as Worksheet

For Each sht in ActiveWorkbook.Sheets
  If sht.name <> "Butiker" then
    With Sht.Range("D2")
        If .Value = "" Then
            .Value = Sheets("Butiker").Range("D2").Value
        Else
            v = Application.Match(.Value, Sheets("Butiker").Range("D2:D75"), 0)
            If IsNumeric(v) Then
                .Value = Sheets("Butiker").Range("D2:D75").Cells(v + 1, 1).Value
            If Trim(.Value) ="" then Exit Sub
                 If Sht.Range("K2") > 0 Then
                   ' ActiveSheet.PrintOut
                   MsgBox ("Printed")
                End If
            Else
                .Value = ""
            End If
        End If
    End With 
  End if
Next sht
End Sub

 

[Weeble]

Sheet will always be "Butiker", I have a drop down list in D2 that can varey in length. My explination was abit bad.

Sheet is fixed on 'Butiker' drop down list is in D2.

This code

[COLOR=#333333]  Else[/COLOR]            v = Application.Match(.Value, Sheets("Butiker").Range("D2:D75"), 0)
            If IsNumeric(v) Then
                .Value = Sheets("Butiker").Range("D2:D75").Cells(v + 1, 1).Value
                If Sheets("Utskrift").Range("K2") > 0 Then
                   ' ActiveSheet.PrintOut
                   MsgBox ("Printed")
                End If [COLOR=#333333]            Else[/COLOR]

goes to next item in the list.. I think this is the part that needs to modify in able to FOR loop.

 

[nemmi69]

Try

LastRowNo = [COLOR=#333333]Sheets("Butiker")[/COLOR].Range("D65536").End(xlUp).Row 'Starts half way down the sheet upwards
[COLOR=#333333][COLOR=#333333] 
 Else[/COLOR]            v = Application.Match(.Value, Sheets("Butiker").Range("D2:D" & LastRowNo), 0)
            If IsNumeric(v) Then
                .Value = Sheets("Butiker").Range("D2:D" & [/COLOR]LastRowNo[COLOR=#333333]).Cells(v + 1, 1).Value
                If Sheets("Utskrift").Range("K2") > 0 Then
                   ' ActiveSheet.PrintOut
                   MsgBox ("Printed")
                End If [COLOR=#333333]            Else[/COLOR][COLOR=#333333]
[/COLOR]
[/COLOR]

 

[Weeble]

Thank you Nemmi69. I figued it out eventualy. I was too blurry when I tried to explained I feel.
This is how I solved it.

Sub NastaButik()    Dim v As Variant
    With Sheets("Utskrift").Range("D2")
     '   If .Value = "" Then
           .Value = Sheets("Butiker").Range("D2").Value
      Do While Sheets("Utskrift").Range("D2") > ""
            v = Application.Match(.Value, Sheets("Butiker").Range("D2:D75"), 0)
            If IsNumeric(v) Then
                .Value = Sheets("Butiker").Range("D2:D75").Cells(v + 1, 1).Value
                If Sheets("Utskrift").Range("L2") > 0 And Sheets("Utskrift").Range("L3") > 0 Then
                   ActiveSheet.PrintOut From:=1, To:=1
                   'MsgBox ("Printed")
                   Else
                   ActiveSheet.PrintOut From:=1, To:=2
                End If
            Else
                .Value = ""
            End If
        Loop
        
      
    End With
End Sub

Thank you for the support though!