VBA code for cycling down through rows and update column based on fixed cell values

[Sixaside]

Hi, I'm sure this is simple but I haven't been able to modify any VBA code I've found to do what I want. Hopefully someone can help.

What I need to do is copy the values in A2:C2 to G2:I2, and then copy the value of M2(value, not the formula) to D2. Then I want to move onto copying A3:C3 to G2:I2 and then copy the value of M2 to D3. So basically cycle down through columns A:C until there is no more data in those columns and each time copy the values of the new row to G2:I2 and copy E2 to column D for the row in question. Columns E and G:I will only ever be populated in row 2 at any time. Screenshot included. Any help much appreciated.

 

[Sixaside]

Correction: in the textbox in the image H2:R2 should read H2:J2 and H3:R3 should read H3:J3.

 

[Peter_SSs]

I was going to upload but can't see an option to do that

No, we don't have workbook uploads in this forum, but we do have XL2BB

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Sixaside]

Thanks for the tip. Minisheet below. Hope someone can help. The cell references in my opening post can be ignored. Details of what I want to do as follows:

I want to copy the value of L2 (not the formula) to D2 after firstly copying the values of A2:C2 to P2:R2. Then I want to copy the values in AC:C3 to P2:R2 and then copy the new value of L2 to D3 and then repeat for each row where there is data in columns A to C.
Some context:
P4 to R6 is a matrix based on values in P2:R2.
P9:R11 is the inverse of the matrix.
H2:J2 is the MMULT of E2:G2 and the matrix inverse.
H3:J3 is the MMULT of E3:G3 and the same matrix inverse. Et. etc.
When the values in P2:R2 change, all the values in H:J change and thus the value in L2 changes. It is this value I want to paste in the appropriate row in column D

Workbook1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	A	B	C		E	F	G	H	I	J		L				1	2	3
2	6.34	4.57	1.63		1	0	-100	1080	384	293		25				6.34	4.57	1.63
3	3.28	3.44	2.52		2	0	-100	1015	361	276
4	1.35	6.35	10.07		4	0	-100	949	338	260				1		-0.63	1.00	1.00
5	2.28	3.60	3.61		5	0	-100	884	315	243				2		1.00	-3.57	1.00
6	4.02	3.81	2.08		7	0	-100	819	292	227				3		1.00	1.00	-5.34
7	1.94	3.82	4.60		8	0	-100	753	270	210
8	5.29	4.58	1.71		10	0	-100	688	247	194
9	3.44	3.28	2.52		11	0	-100	623	224	177						-43.5549	-15.2667	-11.0175
10	1.60	4.47	6.93		13	0	-100	557	201	161						-15.2667	-5.64644	-3.91709
11	1.70	4.12	6.09		14	0	-100	492	178	144						-11.0175	-3.91709	-2.98459
12	1.38	5.84	10.10		16	0	-100	427	155	128
13	1.63	4.78	5.86		17	0	-100	361	132	111
14	3.19	3.51	2.53		19	0	-100	296	109	95
15	2.76	3.29	3.05		20	0	-100	231	86	78
16	3.29	3.82	2.34		22	0	-100	165	63	62
17	1.70	4.23	5.83		23	0	-100	100	41	45
18	2.87	3.29	2.93		25	0	-100	35	18	29
19	6.87	4.58	1.60		30	0	-100	-205	-66	-32
20	5.05	3.99	1.84		35	0	-100	-423	-143	-87
21	3.96	3.43	2.23		40	0	-100	-640	-219	-142
22	9.56	5.05	1.45		45	0	-100	-858	-295	-197
23	3.38	3.48	2.44		50	0	-100	-1076	-372	-252
24	2.86	3.50	2.79		55	0	-100	-1294	-448	-308
25	1.54	4.77	7.43		60	0	-100	-1512	-524	-363
26	1.35	6.35	10.07		65	0	-100	-1729	-601	-418
27	3.80	3.70	2.18		70	0	-100	-1947	-677	-473
28	3.07	3.44	2.65		75	0	-100	-2165	-753	-528
29	1.54	5.03	6.90		80	0	-100	-2383	-830	-583
30	1.48	5.01	8.45		85	0	-100	-2600	-906	-638
31	1.83	3.98	5.11		90	0	-100	-2818	-982	-693
32	7.98	5.59	1.46		95	0	-100	-3036	-1059	-748
33	2.54	3.38	3.29		100	0	-100	-3254	-1135	-803
34	1.70	4.43	5.55
35	1.82	4.03	5.05
36	1.66	4.24	6.36
37	1.58	4.22	7.92
38	1.96	4.02	4.25
39	10.56	5.81	1.38
Sheet1

Cell Formulas
Range	Formula
H2:J33	H2	=MMULT(E2:G2,P$9:R$11)
L2	L2	=MAXIFS(E:E,H:H,">0")
P4:R6	P4	=IF(P$1=$N4,(SMALL($P$2:$R$2,$N4)-1)*-1,1)
P9:R11	P9	=MINVERSE(P4:R6)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N14:S21	Cell Value	=0	text	NO
N14:S21	Cell Value	<0	text	NO
N14:S21	Cell Value	>0	text	NO

 

[Peter_SSs]

Sorry, I have been away for a couple of weeks so have not been able to respond. Thanks for updating your profile and for the XL2BB sample.

If you still need help with this, give this code a try with a copy of your workbook.

Sub CycleRows()
  Dim rw As Range

  For Each rw In Range("A2", Range("C" & Rows.Count).End(xlUp)).Rows
    Range("P2:R2").Value = rw.Value
    Range("D" & Rows.Count).End(xlUp).Offset(1).Value = Range("L2").Value
  Next rw
End Sub

It produced these results for me:

Sixaside.xlsm
	A	B	C	D
1	A	B	C
2	6.34	4.57	1.63	25
3	3.28	3.44	2.52	70
4	1.35	6.35	10.07	13
5	2.28	3.60	3.61	60
6	4.02	3.81	2.08	50
7	1.94	3.82	4.60	40
8	5.29	4.58	1.71	30
9	3.44	3.28	2.52	70
10	1.60	4.47	6.93	22
11	1.70	4.12	6.09	25
12	1.38	5.84	10.10	13
13	1.63	4.78	5.86	25
14	3.19	3.51	2.53	70
15	2.76	3.29	3.05	80
16	3.29	3.82	2.34	60
17	1.70	4.23	5.83	25
18	2.87	3.29	2.93	85
19	6.87	4.58	1.60	22
20	5.05	3.99	1.84	35
21	3.96	3.43	2.23	55
22	9.56	5.05	1.45	14
23	3.38	3.48	2.44	65
24	2.86	3.50	2.79	75
25	1.54	4.77	7.43	20
26	1.35	6.35	10.07	13
27	3.80	3.70	2.18	55
28	3.07	3.44	2.65	75
29	1.54	5.03	6.90	22
30	1.48	5.01	8.45	17
31	1.83	3.98	5.11	35
32	7.98	5.59	1.46	17
33	2.54	3.38	3.29	70
34	1.70	4.43	5.55	30
35	1.82	4.03	5.05	35
36	1.66	4.24	6.36	25
37	1.58	4.22	7.92	19
38	1.96	4.02	4.25	45
39	10.56	5.81	1.38	13
Sheet3