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.

 

[Z51]

Change "$A$2" to just "$A2" so the row number updates as you fill down. When you fill across columns, the $A will stay static.

 

[Saher Naji]

Change "$A$2" to just "$A2" so the row number updates as you fill down. When you fill across columns, the $A will stay static.

Great idea, and in fact it makes the work easier

 

[alansidman]

Option Explicit

Sub Boxes()
    Dim lr As Long, lc As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    For i = 2 To lr
        lc = Cells(1, Columns.Count).End(xlToLeft).Column
        Range(Cells(i, 2), Cells(i, lc)).Value = Range("A" & i)
    Next i
End Sub

 

[Saher Naji]

Option Explicit

Sub Boxes()
    Dim lr As Long, lc As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    For i = 2 To lr
        lc = Cells(1, Columns.Count).End(xlToLeft).Column
        Range(Cells(i, 2), Cells(i, lc)).Value = Range("A" & i)
    Next i
End Sub

This is what I want,
it just need small edit to be perfect, I tried to do it myself by I couldn't, (Check this new attachment please)
and how could I use it for tens of files?
Could I save it as a separated file, and import it when I need it?

Thanks a lot ♥

 

[alansidman]

Don't understand your picture. Suggest you explain step by step what you want. It is always a good idea to show us an actual representation of what you want from the beginning. Because what you have done is change up what the expected results are and causes more work -- tell us up front what it is that you need. Also, if you use the XL2BB function you can attach a piece of the file and we don't have to guess at what formatting is in a picture. We cannot manipulate data that is in a picture and since your "new" request appears to be more complicated, a sample using XL2BB is the way to go. Also, show us what the expected results are.

 

[Saher Naji]

thank you, in fact your code is working very well, but I need to apply it to the original document, this is the first time I tried using XL2BB, I hope it works

Sample File Excel-formula included.xlsx
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1
2
3
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	Box 11 - Case QTY	Box 12 - Case QTY	Box 13 - Case QTY	Box 14 - Case QTY	Box 15 - Case QTY
5	--	None Required	1	3300	1	0
6	--	None Required	1	155	1	0
7	--	None Required	1	4000	1	0
8	--	None Required	1	999	1	0
9	--	None Required	1	300	1	0
10	--	None Required	1	4445	1	0
11	--	None Required	1	2323	1	0
12	--	None Required	1	234324	1	0
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Tabelle1

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L5:L12	Expression	=ISERROR(L5)	text	NO
L5:L12	Expression	=OR((L5 <> K5), (INT(L5) <> L5))	text	NO
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:AB31	Whole number	>=0
N16:N31	Whole number	>=0

No what I want is to apply your VBA code to this new workbook,

 

[alansidman]

You have provided all the formatting but none of the data. Still need to understand what you want to do and the expected results. Please explain. Post #7 is of no hlep.

 

[Saher Naji]

You have provided all the formatting but none of the data. Still need to understand what you want to do and the expected results. Please explain. Post #7 is of no hlep.

Sorry for that,
I"m going to explain from the beginning

what I want:

just to take the values from each row in column J and put them in each respective cell of rows N until the end of columns (in this case AB)

while being able to account for an infinite amount of rows in column J and total amount of columns, as those values change depending on how many products and how many boxes will be shipped

the script then just has to be easily applied to new sheets of the same format

So in short words, it is repeating the values from Column J to the columns from N to the last column headed by Box #-Case QTY

I hope that's my point is clear now

Thanks

 

[alansidman]

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.