IF command + loop into VBA

[krass]

Help appreciated

I have a growing Worksheet (daily update) which has an IF command (in Col L) to calculate the difference between two cells in Col E - but only if value in Col A is identical. The IF command looks like this: =IF(A2<>A1;E2-0;E2-E1) and is placed in Col L

If the worksheet has been sorted eg. by Date, it would be nice to have it sorted by User number first, else, I believe nothing would work?

	A	B	C	D	E	F	G	H	I	J	K	L
1	User	District	Date	Number	Consumption	Temp1	Temp2	Temp3	Temp4	Temp5		Diff
2	100	1	27-07-2018	78017332	0,199	32,59	63,43	30,48	38,6	-8,12	-0,0162	0,199
3	100	1	28-07-2018	78017332	0,205	32,7	63,27	30,61	38,6	-7,99	-0,0164	0,006
4	100	1	29-07-2018	78017332	0,209	32,62	63,34	30,49	38,6	-8,11	-0,0169	0,004
5	100	1	30-07-2018	78017332	0,215	32,6	63,32	30,51	38,6	-8,09	-0,0174
6	100	1	31-07-2018	78017332	0,223	32,78	63,42	30,6	38,6	-8	-0,0178
7	100	1	01-08-2018	78017332	0,226	32,66	63,36	30,59	38,6	-8,01	-0,0181
8	100	1	02-08-2018	78017332	0,234	32,77	63,36	30,46	38,6	-8,14	-0,019
9	200	1	01-07-2018	78171036	0,014	29,36	60,98	31,71	39,2	-7,49	-0,001
10	200	1	02-07-2018	78171036	0,025	29,45	60,27	30,14	39,5	-9,36	-0,0023
11	200	1	03-07-2018	78171036	0,039	29,42	60,53	29,82	39,2	-9,38	-0,0037
12	200	1	04-07-2018	78171036	0,052	29,42	60,53	30,26	39,2	-8,94	-0,0046
13	200	1	05-07-2018	78171036	0,069	29,96	60,61	30,3	39,2	-8,9	-0,0061
14	200	1	06-07-2018	78171036	0,091	30,45	61,09	30,35	39,2	-8,85	-0,0081
15	200	1	07-07-2018	78171036	0,112	30,57	61,59	30,48	38,9	-8,42	-0,0094
15	200	1	08-07-2018	78171036	0,135	31,04	61,76	30,48	38,9	-8,42	-0,0114
17	200	1	09-07-2018	78171036	0,153	31,03	61,79	30,42	38,9	-8,48	-0,013

<tbody>
</tbody>

Hope to get some help

Kim

 

[Special-K99]

You can change that formula to

=E2-IF(A2<>A1;0;E1)

 

[krass]

Thx Special-K99
Yes, gives me the same result but I still have to make sure the the code is in every row. I was hoping for a VBA code that could run automatically. The number of rows today are round 100.000 and added by apx 3000 rows daily.

Sorry

 

[Fluff]

How about

Sub AddFormula()
   Range("A1").CurrentRegion.Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
   Range("L2", Range("K" & Rows.Count).End(xlUp).Offset(, 1)).Formula = "=E2-IF(A2<>A1,0,E1)"
End Sub

 

[krass]

Simply perfect Fluff - I'm very happy

 

[Fluff]

Glad we could help & thanks for the feedback