Hey everyone,
I'm running Excel 2013 on a Windows 7 64-bit.
I have to make labels for my work and one customer in particular tends to multiple labels for their product every time we make it. To save me from having to manual enter them every time, I started making a program that will fill the cells with the correct labels.
Each label has three parts. The part number, the revision level, and the quantity needed. I have a userform on open that prompts you to enter that information and then add it to a list box and enter all the labels. Because there are (7) columns, you can check a check box and have it make even rows. So if you need two and it's checked, you'll get seven. If you need 14, you'll get 14, whether it's checked or not, because it's already a multiple of 7. If it's unchecked, you'll get exact quantities.
Because I may have already cut some labels off the sheet, there is a box, preset to a full sheets worth, where you can enter the number of cells left.
When you click 'Make', it tabs to the cell that is the cumulative quantity of all the labels combined. So if there are 133 labels to a sheet and you need to make 100 labels, then it goes to the 100th cell and fills the data, working backward to the first cell.
The problem I ran into is that it works with (1) sheet worth of labels, but I don't have a way to handle any remainders. So if I have 100 labels left and I need 75 of label1 and 29 of label2, then It'll either break at 100 or you'll just end up with those 100 and you'll have to figure out how many to make when you run it again.
My thought on fixing this was to have it run each label and subtract the quantity run successfully from they quantity that the user input to make. So if they needed 75 of label1, then when it ran 75, it would change that value in the list box to (0). Then it would start running the next label. Once it reached the quantity left number, it's subtract what's left from what's there. So it would run 75 labels and set that to (0). It would run the 29 labels, but when it hits 25 labels, it'll know that there were only 100 left, so it'll subtract and end up with (4) labels remaining in the listbox. Then it'll give the user the option to print and to choose how many labels are on the next sheet you'll print on. It'll go through the process again, adding the last (4) labels. Then you can print them out.
Because it spans multiple sub routines, I declared a few public variables. Everything seems to be working, but I keep getting a type mismatch when I go to change the value of the listbox.
I ended up declaring all of these as variants, because I think that lets them be what they need to be. I made k a public variable, because that's the letter in my For/Next loop that grabs each quantity and starts making the labels. When it goes to make the labels, it knows tells the fill data subroutine that k is the row. The column is always 2.
The problem comes when it goes to change the value of row (k) and column (2). I have:
I tried the bottom 3 lines first. You can see where I commented out the last part of the first message box and tried to replace it with a variable. It breaks above that with the intNum part. intNum is a variant. lstParts(k,2).value should return the value of whatever row it's currently working with, in this case, there is only one row, so it should return the value of lstParts row(0) and column(2), which is 140. Then it should subtract intQty, which is how many were available, from that and get (7). Then it should set that row/column to (7). However, it breaks and gives me a type mismatch with the intNum part.
Thanks for any help you can provide. I know it's not as clear as it can be, but it's starting to fuzz my brain, since I've been looking at it too long. I can post all my code, but it's a descent amount and I comment large blocks out till I know I don't need them anymore, so it isn't clean.
I'm running Excel 2013 on a Windows 7 64-bit.
I have to make labels for my work and one customer in particular tends to multiple labels for their product every time we make it. To save me from having to manual enter them every time, I started making a program that will fill the cells with the correct labels.
Each label has three parts. The part number, the revision level, and the quantity needed. I have a userform on open that prompts you to enter that information and then add it to a list box and enter all the labels. Because there are (7) columns, you can check a check box and have it make even rows. So if you need two and it's checked, you'll get seven. If you need 14, you'll get 14, whether it's checked or not, because it's already a multiple of 7. If it's unchecked, you'll get exact quantities.
Because I may have already cut some labels off the sheet, there is a box, preset to a full sheets worth, where you can enter the number of cells left.
When you click 'Make', it tabs to the cell that is the cumulative quantity of all the labels combined. So if there are 133 labels to a sheet and you need to make 100 labels, then it goes to the 100th cell and fills the data, working backward to the first cell.
The problem I ran into is that it works with (1) sheet worth of labels, but I don't have a way to handle any remainders. So if I have 100 labels left and I need 75 of label1 and 29 of label2, then It'll either break at 100 or you'll just end up with those 100 and you'll have to figure out how many to make when you run it again.
My thought on fixing this was to have it run each label and subtract the quantity run successfully from they quantity that the user input to make. So if they needed 75 of label1, then when it ran 75, it would change that value in the list box to (0). Then it would start running the next label. Once it reached the quantity left number, it's subtract what's left from what's there. So it would run 75 labels and set that to (0). It would run the 29 labels, but when it hits 25 labels, it'll know that there were only 100 left, so it'll subtract and end up with (4) labels remaining in the listbox. Then it'll give the user the option to print and to choose how many labels are on the next sheet you'll print on. It'll go through the process again, adding the last (4) labels. Then you can print them out.
Because it spans multiple sub routines, I declared a few public variables. Everything seems to be working, but I keep getting a type mismatch when I go to change the value of the listbox.
I ended up declaring all of these as variants, because I think that lets them be what they need to be. I made k a public variable, because that's the letter in my For/Next loop that grabs each quantity and starts making the labels. When it goes to make the labels, it knows tells the fill data subroutine that k is the row. The column is always 2.
The problem comes when it goes to change the value of row (k) and column (2). I have:
Code:
intNum = lstParts(k, 2).Value
MsgBox ("Old Value: " & intNum) 'lstParts(k, 2).Value)
lstParts(k, 2).Value = lstParts(k, 2).Value - intQty
MsgBox ("New Value: " & lstParts(k, 2).Value)I tried the bottom 3 lines first. You can see where I commented out the last part of the first message box and tried to replace it with a variable. It breaks above that with the intNum part. intNum is a variant. lstParts(k,2).value should return the value of whatever row it's currently working with, in this case, there is only one row, so it should return the value of lstParts row(0) and column(2), which is 140. Then it should subtract intQty, which is how many were available, from that and get (7). Then it should set that row/column to (7). However, it breaks and gives me a type mismatch with the intNum part.
Thanks for any help you can provide. I know it's not as clear as it can be, but it's starting to fuzz my brain, since I've been looking at it too long. I can post all my code, but it's a descent amount and I comment large blocks out till I know I don't need them anymore, so it isn't clean.
