For Each / Do Until Loop Hit a Wall :-(

[lemanstom]

Hi everyone, so I've been working at this problem for a while and can't get the code to produce exactly what I need.

I have a spreadsheet which is essentially going to print to an A4 sheet of 8 labels. On each label I want a customer name and order number, which is input via a UserForm. The UserForm also has a textbox for choosing how many labels you need printed on the sheet (trying to avoid waste instead of printing a full sheet every time when only 1 or 2 are needed).

So, I need the code to essentially loop through specific cells in the sheet (in columns B & D) and populate them with the respective client name or order number (I've gone with giving each type of cell a specific background color in order to target them). When I run the following code, it just populates all the cells, instead of just the quantity specified in the UserForm textbox.

Anyone got a) any clue where I'm going wrong and/or b) a more elegant solution please?

Many thanks, hope this makes sense!

Private Sub PrintButton_Click()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim labelSheet1 As Range
    Dim colorCount As Integer
    
    Set labelSheet1 = wb.Sheets("LabelSheet1").Range("B2:D9")
    
    For Each cell In labelSheet1
        If cell.DisplayFormat.Interior.Color = RGB(253, 253, 253) Then
            colorCount = 1
            Do Until colorCount > Me.BoxQuantity.Value
            cell.Value = Me.DistributorName
            colorCount = colorCount + 1
            Loop
        End If
        If cell.DisplayFormat.Interior.Color = RGB(254, 254, 254) Then
            colorCount = 1
            Do Until colorCount > Me.BoxQuantity.Value
            cell.Value = "#" & Me.OrderNumber
            colorCount = colorCount + 1
            Loop
        End If
    Next cell
    Unload Me
End Sub

 

[steve the fish]

Id expect it to fill in every cell because the for loop is looping through every cell. The do loop is just filling in the same cell multiple times. I wouldnt use color to determine which cells to fill. In B2 to D9 what is it you expect to see in those cells? Give an example by copying from excel and then pasting here. Is column B for example the distributor then D the order number?

 

[lemanstom]

Thanks Steve, I appreciate your response. Here's a mini sheet so you can see what the end result should be

Brush Box Labels.xlsm
	A	B	C	D	E
1
2		Test Distributor		Test Distributor
3		#123456		#123456
4		Test Distributor		Test Distributor
5		#123456		#123456
6		Test Distributor		Test Distributor
7		#123456		#123456
8		Test Distributor		Test Distributor
9		#123456		#123456
10
LabelSheet1

 

[Fluff]

How about

Private Sub PrintButton_Click()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim i As Long
    
   With wb.Sheets("LabelSheet1")
      For i = 1 To Me.BoxQuantity.Value
         Select Case True
            Case i Mod 2 = 1
               .Cells(i + 1, 2) = Me.DistributorName
               .Cells(i + 2, 2) = "#" & Me.OrderNumber
            Case Else
               .Cells(i, 4) = Me.DistributorName
               .Cells(i + 1, 4) = "#" & Me.OrderNumber
         End Select
      Next i
   End With
   Unload Me
End Sub

 

[lemanstom]

@Fluff this is perfect. Thanks so much, rapid response. I've been banging my head on the desk for hours as I didn't want to bother the forum, this works perfect, thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.