Auto-Fill values

[Saher Naji]

Hello

Thanks in advance,

I tried to crate a formula, but the problem I have to make the formulas fixed by $ symbol, this will cost me a long time

What I'm looking for is a formula or a macro to do this thing automatically each time, because I have hundreds of rows, and the number of boxes (columns) is not fixed, some times I have 50 boxes or more.

Check the attached screenshot please,

Best.

 

[Saher Naji]

Ok. Got it now. Thanks for being clear.

Sub Boxes()
    Dim lr As Long, lc As Long
    Application.ScreenUpdating = False
    lr = Range("J" & Rows.Count).End(xlUp).Row
    Dim i As Long
    For i = 2 To lr
        lc = Cells(4, Columns.Count).End(xlToLeft).Column
        Range(Cells(i, 14), Cells(i, lc)).Value = Range("J" & i)
    Next i
    Application.ScreenUpdating = True
End Sub

Create a Personal.xlsb file and store this in that file. Google Ron deBruin Personal.xlsb and follow his instructions for creating and using in multiple files.

Perfect, but I want to keep the headers as they are, just I want to repeat the values

Sample File Excel-formula included.xlsx
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
4	Units per Case	Units per Case	Units per Case	Units per Case	Units per Case	Units per Case	Units per Case	Units per Case	Units per Case	Units per Case	Units per Case	Units per Case	Units per Case	Units per Case
5	3300	3300	3300	3300	3300	3300	3300	3300	3300	3300	3300	3300	3300	3300
6	155	155	155	155	155	155	155	155	155	155	155	155	155	155
7	4000	4000	4000	4000	4000	4000	4000	4000	4000	4000	4000	4000	4000	4000
8	999	999	999	999	999	999	999	999	999	999	999	999	999	999
9	300	300	300	300	300	300	300	300	300	300	300	300	300	300
10	4445	4445	4445	4445	4445	4445	4445	4445	4445	4445	4445	4445	4445	4445
11	2323	2323	2323	2323	2323	2323	2323	2323	2323	2323	2323	2323	2323	2323
12	234324	234324	234324	234324	234324	234324	234324	234324	234324	234324	234324	234324	234324	234324
Tabelle1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N16:CG984,O5:CG15	Expression	=OR(AND(NOT(ISNUMBER(N5)), NOT(ISBLANK(N5))), (N5 < 0))	text	NO
N16:CG984,O5:CG15	Expression	=(INT(N5) <> N5)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
O5:AA12	Whole number	>=0

 

[alansidman]

Sorry, did not change this line of code

For i = 2 To lr

change to

For i = 5 To lr

 

[Saher Naji]

Working 100%., thank you very much,
Super
now I'm going to Google Ron deBruin Personal.xlsb and follow his instructions for creating and using in multiple files. ♥

 

[Saher Naji]

Sample File Excel_corrected.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2
3
4	Who will label?	Expected QTY	Units per Case	Number of Cases	Boxed Case QTY		Box 1 - Case QTY	Box 2 - Case QTY	Box 3 - Case QTY	Box 4 - Case QTY	Box 5 - Case QTY	Box 6 - Case QTY	Box 7 - Case QTY	Box 8 - Case QTY	Box 9 - Case QTY	Box 10 - Case QTY	Box 11 - Case QTY	Box 12 - Case QTY	Box 13 - Case QTY	Box 14 - Case QTY	Box 15 - Case QTY	Box 16 - Case QTY	Box 17 - Case QTY	Box 18 - Case QTY	Box 19 - Case QTY
5	None Required	1	10	5	50		10	10	10	10	10
6	None Required	1	15	4	60							15	15	15	15
7	None Required	1	20	10	200											20	20	20	20	20	20	20	20	20	20
8
9
10
11
Tabelle1

Cell Formulas
Range	Formula
L5:L7	L5	=SUM(N5:AG5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L5:L7	Expression	=ISERROR(L5)	text	NO
L5:L7	Expression	=OR((L5 <> K5), (INT(L5) <> L5))	text	NO
N5:AG7	Expression	=OR(AND(NOT(ISNUMBER(N5)), NOT(ISBLANK(N5))), (N5 < 0))	text	NO
N5:AG7	Expression	=(INT(N5) <> N5)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
N5:AF7	Whole number	>=0

Hello,

in fact your solution was excellent, but there is a wrong in my explanation, it's really my mistake and I'm sorry for that
actually I need the values repeated according to a specific criteria,
The code you created is true, but I need to edit it to be fit with the column K, so if I have 5 cases in column K, this mean it supposed to repeat the Units Value (J) 5 times, then in the second row, if the value (number of cases) in column (K)=4, is supposed to repeat the value 4 times but after the repeated values for the previous row

I hope that my point is clear

Thanks a lot

 

[alansidman]

Try this:

Option Explicit

Sub Boxes()
    Dim lr As Long, lc As Long, x As Long
    Application.ScreenUpdating = False
    lr = Range("J" & Rows.Count).End(xlUp).Row
    Dim i As Long
    For i = 5 To lr
        x = Range("K" & i) - 1
        lc = Cells(4, Columns.Count).End(xlToLeft).Column
        Range(Cells(i, 14), Cells(i, 14 + x)).Value = Range("J" & i)
    Next i
    Application.ScreenUpdating = True
End Sub

Book1
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
4	Prep Type	Who will label?	Expected QTY	Units per Case	Number of Cases	Boxed Case QTY		Box 1 - Case QTY	Box 2 - Case QTY	Box 3 - Case QTY	Box 4 - Case QTY	Box 5 - Case QTY	Box 6 - Case QTY	Box 7 - Case QTY	Box 8 - Case QTY	Box 9 - Case QTY	Box 10 - Case QTY
5	--	None Required	1	3300	1	3300		3300
6	--	None Required	1	155	2	310		155	155
7	--	None Required	1	4000	3	12000		4000	4000	4000
8	--	None Required	1	999	4	3996		999	999	999	999
9	--	None Required	1	300	5	1500		300	300	300	300	300
10	--	None Required	1	4445	6	26670		4445	4445	4445	4445	4445	4445
11	--	None Required	1	2323	7	16261		2323	2323	2323	2323	2323	2323	2323
12	--	None Required	1	234324	8	1874592		234324	234324	234324	234324	234324	234324	234324	234324
Sheet1

Cell Formulas
Range	Formula
L5:L12	L5	=SUM(N5:AG5)

 

[Saher Naji]

Great and very close to the goal, but could we edit it to start each case after the previous one is done, if you notice the example I sent, Please check this XL2BB

Sample File Excel_corrected.xlsx
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2
3
4	Units per Case	Number of Cases	Boxed Case QTY		Box 1 - Case QTY	Box 2 - Case QTY	Box 3 - Case QTY	Box 4 - Case QTY	Box 5 - Case QTY	Box 6 - Case QTY	Box 7 - Case QTY	Box 8 - Case QTY	Box 9 - Case QTY	Box 10 - Case QTY	Box 11 - Case QTY	Box 12 - Case QTY	Box 13 - Case QTY
5	10	5	50		10	10	10	10	10
6	15	4	60							15	15	15	15
7	20	10	180											20	20	20	20
8
9
10
11
12
13
Tabelle1

Cell Formulas
Range	Formula
L5:L7	L5	=SUM(N5:AG5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L5:L7	Expression	=ISERROR(L5)	text	NO
L5:L7	Expression	=OR((L5 <> K5), (INT(L5) <> L5))	text	NO
AG7,N7:AE7,N5:AG6	Expression	=OR(AND(NOT(ISNUMBER(N5)), NOT(ISBLANK(N5))), (N5 < 0))	text	NO
AG7,N7:AE7,N5:AG6	Expression	=(INT(N5) <> N5)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
N5:Z7	Whole number	>=0

 

[alansidman]

Need to think about this a bit. It is not intuitive to me. Issue with the last column. Will the last column always be the same? What happens when you reach the last column and there are still lines of data to be accounted for? In your example you have 10 columns of cases. Tell us more.

 

[Saher Naji]

for example if I have 1 case in the 1st row, 2 cases in the 2nd row, and 3 cases in the 3rd row (like the example you sent) this mean 6 cases in total, and this mean I need 6 boxes, and this exactly equal the number of required columns. one column for the first case (Box 1 - Case QTY), 2 columns next to the first column for the the second case because it's (2), and 3 columns next the the previous cases's columns for the 3rd case because the value there is 3.

in other words, the sum of values in column for example Sum(O:O) should equal (10) in this case, and it suppose to be just one number in this column

if you asking about the headers don't worry about this because each time I receive this sheet I receive it edited depending on the number of cases, suppose that I will got 225 cases today, this mean I will receive a new sheet with 225 empty columns from [Box 1 - Case QTY] to [Box 225 - Case QTY], only the headers are there without values.

I thank you from my heart for the great help

 

[alansidman]

Here is a revised code.

Option Explicit

Sub Boxes()
    Dim lr As Long, lc As Long, x As Long, y As Long
    Application.ScreenUpdating = False
    lr = Range("J" & Rows.Count).End(xlUp).Row
    Dim i As Long
    y = 14
    For i = 5 To lr
        x = Range("K" & i) - 1
        lc = Cells(4, Columns.Count).End(xlToLeft).Column
        Range(Cells(i, y), Cells(i, y + x)).Value = Range("J" & i)
        y = y + x + 1
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Saher Naji]

Thank you very much, it's perfect, is there a way to pay to you, or at least rate you here in this Forums?