Hi
We have an oil tank which we fill on different dates at different prices.
As the oil gets burned the remaining oil is calculated by entering the fuel height in J29. (First volume consumed first).
A macro (call it Reset) is needed to clear the burned volume in columns CDE and make room for additional fills, as in the attached pictures.
(Custom formatting is used in column J.
Thanks
.
If I understand your question correctly, you are wanting to delete (clear) the contents of the specified cells ? A macro seems overkill since
you can highlight the cells to be cleared / right click / CLEAR CONTENTS ... and its done.
However, here is a macro to do the same: Highlight the cells to be cleared, then click the button.
Please Login or Register to view this content.
No. Not that simple...
In this example, C33:E34 are shifted up to c31, and 1302 liters replaced with 120. c31:E32 is deleted / overwritten since they are consumed.
But this is not fixed. If you change the number in j29 to 90, the only thing for the macro to do is change D31 to 516.11
drgkt,
In a standard module, place this code then call it from a button or how you choose
HTH,Please Login or Register to view this content.
Maud
Please note also that liters 1302 change to what is left of them --> 120
p.s. DO NOT do entire row delete
Last edited by drgkt; 01-10-2017 at 06:37 PM.
The logic (as I understand it) would be:
step 1
From the range c31:e40, exclude the cells that do not meet the criteria in post 4 and copy as values only to C31.
step 2
copy k31 to d31 as value only.
Try this:
Please Login or Register to view this content.
some error....
1.jpg
See if this is how you wanted.
Please Login or Register to view this content.
Perfect!
Could you please include the following in the code:
Copy J4:j26 to i4:i26 as values only, overwritting values of i4:i26
Then delete entries in J4:J26, retaining formats.
Thanks!
edit: [OBSOLETE]: Disregard.
Last edited by drgkt; 01-11-2017 at 04:14 PM.
Is this what you mean?
Please Login or Register to view this content.
Well, I spoke too soon...Originally Posted by jindon
See if this is how you wanted.
Please Login or Register to view this content.
When J29 is 20, the macro works like it supposed to.
BUT when J29 is 90, then the macro's job is to ONLY copy k31 to d31 (Values Only).
INSTEAD, it clears contents in c31:e34
Last edited by drgkt; 01-11-2017 at 02:39 PM.
Bringing it back in focus...
Anyone???
Need to see the result when J29 = 90.
What do you mean?
Just enter 90 in J29 in the worksheet provided in post 1, then run the macro.
I need to see EXACT result that you want for 90.
When 20, I took all the lines that is above 0 in Height(col.J).
When J29=90, then the only thing the macro needs to do is copy k31 to d31 values only.
Then I can not find the logic, so wait for someone else to comes in to help you.
Otherwise need a crystal clear logic that we can understand.
Ok.
When j29=20, k31 and k32 are zero. That means that the 1st and 2nd filling have been burned completely.
The 3rd filling, has not been completely burned, there are still 120 liters left.
So, we get rid of the completely burned fillings by overwritting c31:e32 with c33:e34* and THEN copying k31 to d31 values only. (i.e. change 1302 to 120).
However, when j29=90, none of the fillings has been burned completely; so the only thing to do is copy k31 to d31 values only.
YET if j29=91.4 (G29); then NOTHING has been consumed. So the macro needs to exit doing NOTHING.
I hope it is clear.
*edit: e40 because there might be up to 10 fillings. (We overwrite values only otherwise the borders will be messed up).
EVENMORE, if j29=0 then all fillings have been consumed so, c31:e40 must be blank after macro execution.
Last edited by drgkt; 01-12-2017 at 04:29 AM.
Hope this works as you want.
Please Login or Register to view this content.
Hi there!
I appreciate your effort.
Sorry for the delay, running tests...
Try this:
j29=16
run macro
c31 should be 2017-01-02 ok
c32 should be 508.37 ok
c33 should be 0.97 ok
c32:e40 should be blank. It is not
j31 formula changed from g31-(g29-j29) to if(j30>=0.....
Here is an updated file that turns red the fillings that need to be overwritten depending on the J29 value.
(Take a look on the conditional formatting formula)
Last edited by drgkt; 01-12-2017 at 06:34 PM.
OK
Try this
Please Login or Register to view this content.
gee
with j29=0 the last filling is not erased because there are 0,00000000000011 liters left!
That's why I made the notation in post 22 about the conditional formatting in which I used rounding to 2 decimals for liters.
p.s. Download version in post 22
Don't know what you are talking about.
When J29=0, it should clear all the entries.
See.
I added a 5th filling
I meant to include the real values of the k column as well
Are we talking about the same data set?
Was my attachment working or not?
Yours is working because it has only 4 fillings, I added a fifth.
There could be as many as 10.
Upload a workbook with 10 fillings, otherwise it will be just wasting time.
I think the problem is not in the number of fillings (you can use random dates, liters, and prices).
If you are looking in value in column k to be exactly zero, then that is why the 5th filling is not picked up. K35 as you can see above in the pics (in Q35) is not zero, it is 0,00000000000011 liters.
If you still want me to upload a file with 10 fillings, please tell me.
My code is looking at col.H not col.K.
If Col.D - Col.H = 0 then Clear, else Col.D will be replaced with the balance.
hmmm... I am scratching my head...
put this as 5th filling 2017-01-12 - 1010 - 0.99 and run the code, you see it is not cleared, EVEN though D35-H35=0. You think it has something to do with the 0,00000000000011 liters left?
Same thing happens for the 10th random filling in my test
OK, found it with 0.99. And you have Event code in the sheet.
Please Login or Register to view this content.
Please explain, what is the matter with 0.99, what Event code and why it matters.
p.s. I assume you mean this sheet1 code
Please Login or Register to view this content.
Last edited by drgkt; 01-13-2017 at 06:20 AM.
1) When subtract calculated value in Col.H from 1010 returns 1.13686837721616E-13.
It is almost 0 but not 0.
So, if you want leave the value below 0.5 after the subtraction, you need to change 0 to 2,3,4,5 or 6 etc.
2) You have Selection_Change event code in Sheet1 then disable Event during the code runs for the safety.Please Login or Register to view this content.
But in a random cell I put if(d35=h35;true;false) and it returns TRUE.
I am not sure what you mean by that.So, if you want leave the value below 0.5 after the subtraction, you need to change 0 to 2,3,4,5 or 6 etc.
Please Login or Register to view this content.
Ok.
• How would I add: If J29 is blank, exit doing NOTHING?
どうもありがとうございます
That's the problem we faced. See https://support.microsoft.com/en-us/kb/214118But in a random cell I put if(d35=h35;true;false) and it returns TRUE.
Add one line
Please Login or Register to view this content.
Interesting!
Just explain this please:
I am not sure what you mean by that.So, if you want leave the value below 0.5 after the subtraction, you need to change 0 to 2,3,4,5 or 6 etc.
Please Login or Register to view this content.
ありがとう
Application.Round method is used for fixing the problem after the calculation.
If the result is below 0.5 in LTTERS, it round down to 0.
If you want to avoid it the second argument for Round method needs to be greater than 2. otherwise it will treat it as 0.
Do you understand?
It will show! (Read below)
The way you have it: "If Application.Round(Cells(x(i), 2), 2) <> 0 Then"
It means that if dx-hx <=0.5 liters it will be treated as zero.
Correct?
I do not understand what will happen if I change "0" with 1,2,3,4,5 or 6 etc.
I don't think you concern about the LTTR less than 0.0000xxx, so I guess setting the second argument to 5 is good enough.
1. You mean modify it lke this: "If Application.Round(Cells(x(i), 2), 5) <> 0 Then" ?
2. More errors! Note that the same exact code, as you last wrote it (I only added if j29 blank, exit), on a different workbook gave the run-time error.
The last foto is the different workbook, the error and the code highlighted by the bebugger.
Picture doesn't help at all.
If you post a workbook, I will have look.
ok, but in mean time, on the worksheet we are working why all fillings disappeared? Only the 2 top should have.
Don't know, if you are talking about different workbook.
OOps, missed a period before Cells(x(i), 2)
Please Login or Register to view this content.
Ok. Now it works.
Just so I can understand, the way you have it: If Application.Round(.Cells(x(i), 2), 0) <> 0 Then
It means that if Dx-Hx is up to 0.99 is treated as zero?
and if I change it to:If Application.Round(.Cells(x(i), 2), 5) <> 0 Then
It means that if Dx-Hx is up to 0.00099 is treated as zero?
If the 2nd argument is 0, it treat as 0 for the number up to 0.4999999999999....
See the Excel Help about Round function for more details.
And if I use
If Application.Round(.Cells(x(i), 2), 5) <> 0 Then
Numbers up to what are treated as zero?
I am still confused about the rounding.
First things first, I need to understand the syntax
In If Application.Round(.Cells(x(i), 2), 0) <> 0
Red is the first argument, blue is the second ?
What does the first argument do?
What does (.Cells(x(i), 2) mean in english?
Now you say that If the 2nd argument is 0, it treat as 0 for the number up to 0.4999999999999....
I do not understand this 0.5 logic. In ROUND(value,1) I can round to 0.1 NOT 0.5
What if second argument is 1?
If it is 5, up to what number is treated as zero
1. If [..Round(.Cells(x(i), 2), 0)...] is equivalent to Round(Cell#,0) which basically means integer what does the 2 besides the x(i) mean?
2. Just to test, back in our sheet I added 0.01 liter in col. D. The real value in col. K is (height * 31.773) = 0,00999999999999998. Therefore, the difference D-K = 0.0000000000000000156...
When I run the code [..Round(.Cells(x(i), 2), 0)...] it rounds the above to integer 0 and therefore correctly gets rid of that line.
The same happens with [..Round(.Cells(x(i), 2), 1)...].
HOWEVER, if I run the code [..Round(.Cells(x(i), 2), 2)...] which supposedly rounds the above to 2 decimals (0.00), the 0.01 liters DO NOT get erased.
Based on the logic above, I would have to use [..Round(.Cells(x(i), 2), 17)...] to keep that filling!
What am I missing?
Last edited by drgkt; 01-15-2017 at 06:40 AM. Reason: K is the right column
Basically, you are the only one to decide.
I have provided the link for the reason that you need the round function and suggested to read the Excel Help for Round function.
My last suggestion is, you don't have to take it though, use round function on the sheet in column H like
H31: =IF(J31<0,D31,ROUND((G31-J31)*F$28,3))
Done that already in newer version of the book!
Please do me the favor to answer the 2 questions on post 52 because I am DYING from curiosity. WHAT AM I MISSING?
Do a Step debug.
While you are in VBE
Goto [View] - [LocalWindow]
Click on somewhere on the code and hit F8,
It executes one line as you hit F8 and you can see all the variables in the LocalWindow, so that you would be able to see what is going on.
If you did, it will never return the value like D-H = 0.0000000000000000156...
I am running this on the old version for testing.
I do not see anything I can understand here.
You have to forgive me; Most would say Ah it works, the hell with it!
Why are you still running old version?
I think it solved the problem.
code is:
1) Calculate the balance (D-H) and updates col.D. As I said in my post #32.
2) Store row reference in col.D for the row(s) that are not 0. (variable x)
3) move the the data in C-E of row(s) that have >0 to the rop acocordingly.
4) Clear the C-E of the rows that have 0 in col.D.
So Col.H is rounded, it should have no problem.
Last edited by jindon; 01-15-2017 at 06:44 AM.
Like I said, for testing purposes.
My bad, corrected post 52. Calculation is D-K in that post. I will take a look.
In mean time please tell me:
1. What is the 2 in [If Application.Round(.Cells(x(i), 2), 0)...]
2. Am I correct when I say that, in order for a number like 0.0000000000000000156... to be treated NON zero the code must be [If Application.Round(.Cells(x(i), 2), 17)...]
As I replied to your PM already.
1) What have you learned from Excel Help about Round function?
2) I just don't want to count so many 0s. You should find it for yourself using worksheet like I suggested in PM.
Last edited by jindon; 01-15-2017 at 07:16 AM.
ROUND (CELL, DecimalNumber)
Round (.Cells(x(i), 2), 0)
0 is the number of decimal ponts.
What is the 2?
Look at the VBA Help about "Cells".
Last edited by jindon; 01-15-2017 at 07:39 AM.
I am not getting my point across
In round(a1,0) there are 2 arguments a1 and 0
In Round (.Cells(x(i), 2), 0) seem to be 3: a. cells x(i) b. 2 and c. 0
So you know nothing about vba and try to understand the code.
The message box should give you the data range that you are dealing with.
Cells property should reference to the RELATIVE position from the range that you are dealing with, not from the worksheet as a whole.
Please Login or Register to view this content.
FINALLY !!!
Thank God!
Yes, unfortunately not familiar with VBA. I would say it is Greek to me, if I was not Greek!
Had to learn Surgery first, but never too late !!!
Can you tell me where to start? And I mean level ZERO.
Thread is always in the top of forum
http://www.excelforum.com/showthread.php?t=823709
I dedicate this post to Jindon who spent considerable time and effort to accomplish this.
THANK YOU again Jindon!
Gee, you made me second guess my English, having to ask same question 10 different ways!
Is it really that bad?
g
No, it was not your English.
It was my careless reading of your question.
I thought you keep asking about 2nd arg of Round function again & again...
Sorry for that.
Yes, I figured that!
By the way, wouldn't you have any ideas about this?
http://www.excelforum.com/showthread.php?t=1169807
Jindon, why This does not show up on my sheet?
No idea about what you are asking.
Ok. Let me spell it out.
D35-H35 formatted as number to 30 decimals is calculated as zero (O35)
H35 formatted the same way is calculated as 1010 (O39)
H35 (when J35>=0) is (G35-J35)*F28.
G35 formatted as number to 30 decimals is calculated as 31,7879960973153 (O29)
J35 formatted as number to 30 decimals is calculated as 0.0000000000000035527136788005
BUT G35-J35 = G35 (O31) INSTEAD of being 31.7879960973152964472863211995, which will make H35 to be 1009.9999999999989140196282834717 and therefore
D35-H35 = 0.0000000000010859803717165282865
p.s. You mentioned something about the floating ... error but that was a case that while the calculation should be zero it is not.
This is the exact opposite.
Last edited by drgkt; 01-18-2017 at 02:55 AM.
Why do you concern with such small number?
Excel can only accept 15 digits.
Google about something like "Excel 15 digits limit"
Not concerned, just wondered why.
Did not know about the 15 decimal limit, since in format it allows up to 30 decimals.
Thank you again.
p.s.
Does the above mean that if it was 1.13686837721616E-16 (one more than 15) it would treat it as zero?
Last edited by drgkt; 01-19-2017 at 03:50 PM.
Hi Jindon
I needed to move the data as seen below. will you please be so kind to update the code?
Thanks!
Also I see in the code you are referring to a1:c1. Why is that?
1.jpg
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks