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

lemanstom

New Member
Joined
Nov 29, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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!
VBA Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,643
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 29, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1
2Test DistributorTest Distributor
3#123456#123456
4Test DistributorTest Distributor
5#123456#123456
6Test DistributorTest Distributor
7#123456#123456
8Test DistributorTest Distributor
9#123456#123456
10
LabelSheet1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,928
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 
Solution

lemanstom

New Member
Joined
Nov 29, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
@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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,928
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,144,246
Messages
5,723,220
Members
422,485
Latest member
Tameima

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top