IF command + loop into VBA

K

krass

New Member
Joined
Nov 14, 2014
Messages
5
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?


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


Hope to get some help

Kim
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
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 :-)
 
Upvote 0
How about
Code: 
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
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Locked
  • Question Question
Convert Excel IF command to VBA
Replies
2
Views
401
Fluff
Fluff

Forum statistics

Threads
1,226,162
Messages
6,189,344
Members
453,538
Latest member
Kazf201

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top