What the code does: i start the macro, insert one value say 10 and the macro introduces it in column A cel by cell unless the received value is below 50% or above 150% of 10. The values are received from balances.

What i want:1.[b]If WData < 0.5 * GrNom4 or WData > 1.5 * GrNom4 Then[/b] - this is ok but i needed it to skip a value...say 50. So everything below 50% or above 150% from the original value should not be entered in a cell, except for the "50" value.2. A column can have a maximum of 3000 values (it can also have 200).... i want to select 30 random values and calculate their average, and then calculate a formula. Something like AAA * 0.603 - this average = XX.

This is not my code. I am not this good. Any help would be greatly appreciated.Thank you!

Comments

[color=Green]1.If WData < 0.5 * GrNom4 or WData > 1.5 * GrNom4 Then - this is ok but i needed it to skip a value...say 50. So everything below 50% or above 150% from the original value should not be entered in a cell, except for the "50" value.[/color]

Note, that last one is all on one line, you do not need an end if block if you keep the statement all on one line.

[color=Green]2. A column can have a maximum of 3000 values (it can also have 200).... i want to select 30 random values and calculate their average, and then calculate a formula. Something like AAA * 0.603 - this average = XX. [/color]

You can then just call this function to retrieve your values, you can even add more values to the output of the function to make it more useful. You will probably want to input the values into cells, but to display how it works, you could call it with the below code:

The first bit works like a charm. I especially liked the one line code. Very clean ... you sir know your stuff.The second part i didn't get the chance to test, yet. I will do so ASAP.

And i have another question:

How to make "50" appear on the worksheet in the same number cell on column 6 as the last number from column 5. Basically whenever "50" appears, it's placed next to the last occupied cell on column 5, but on column 6. I hope i made myself clear, my English is a bit rusty.

Not 100% sure I understand what you need, but my guess is that going down column E you have some blank values and/or zeros, you wish to ignore these and only put a 50 by the last real number when a 50 is found, so if going down column E you had the values: 23, 44, 50, 67, 0, 0, 50, 55, ""(blank), 50, 616

You would want 50s to appear in Column F along side the 44, 67 and 55.

If that is correct then the below code will achieve what you want. If it is not, please reply back with another explanation of what you're looking for.

I will try to explain better. As you saw in the code, it eliminates anything above 150% or below 50% (because they are considered aberrations). All values are with 2 decimals like "31.26". When i launch the macro it asks for the number: let's say 30. The majority of values will be between 29-31 but there is a list of possible values (say from 29.26 - 30.98). Anything below or above is wrong. This macro is for a balance and a lot of types of products. The weighing process is automatic but supervised by a human operator (in order to decide if the weight is correct). Due to the fact that we have some 200 products to weigh there is no point in making a code for every one (the products may even change from one day to another). So i want to allow the human operator to decide which value is ok.And that excepted value will be some type of 50 grams weight (or another value that does not interfere with the product weight). So if the maximum value is 30.98 and the current product weighs 31.01 than the human operator weighs that excepted value next ; which appears beside the 31.01 value. Same cell number but next column. So now everybody knows that 31.01 is wrong (he/she does not need to know the exact interval only what 50 means). Of course that "50" value (in excel) can be replaced with a text that says "WRONG VALUE" or something.

OK, I think I now know what you mean, I was a bit thrown because I thought you were only looking to do this to randomly selected numbers, but if you are looking to do it to the whole data, I'm thinking you would need to use something like the following:

This is based on columns E and F again, if you wish to put the "50" code in column A, change the 6s to 1s. Hope this really does do what you need, if not, just ask again, maybe cite some more examples, We WILL get there! Regards, Dai

------------------------------------------Do or do not, there is no try. |------------------------------------------

I don't need another input box on column 2. It should be linked to this macro and the values it inputs on column 1.I'll try to build an example. Let's say the "good" values are from 30.20 up to 30.25.So in the end it looks like this:Column A Column B30.2130.2530.2230.2530.2230.2130.27 2330.2230.28 2330.2130.22and so on

But only the human operator decides 30.27 & 30.28 are wrong. He/she sees the values and the next thing he/she weighs is the excepted value (in this case 23). Once 23 has been weighed the macro "knows" to put this value besides the last input value. So the 23 signals bad value. Next value that is not 23 goes in the cell below and so on. This "placing 23 in the adjacent cell" bit needs, if possible, to be inside the already made macro. Not a new macro. This bit helps me keep track of how many products not ok. Quality assurance stuff. PS: i know that on column 2 a time stamp appears... but it shouldn't be a problem because it's linked to that formula on column 1.

OK I think I'm getting a clearer picture now, so the GetSWDataCom14 sub gets called each time something is weighed correct? And WData is the actual weight being pulled from the Com14 program.

So if the weight is below 75% of the initial inputbox value (i.e. GrNom4) or above 175% (or 50 and 150, whatever) then you delete the value completely as if it had never been weighed?

Then if they weigh something that's within these bounds (75% - 175%), but looks too high for the operator, e.g. 30.27, the operator will then weigh a 23 gram weight on the scale as a way of informing the computer that the last value was too high or low, correct?

If yes to all 3 above, then all you need to do is change the following code:

My only worry is that you are overwriting the weight with the average formula every 31 rows, are you sure you want to overwrite it, not put it somewhere else?

Just thinking if you get say a 30.28, on the 31st row, or 62nd etc, then you won't even know about it as it'll just be overwritten with a formula for the averages. You could simply put it on the next line instead so nothing is overwritten?

Also, I'd recommend working out the average in the code itself, then insert the value into the cell rather than a formula, helps speed up Excel if you use less formulae, may not be noticeable on a small workbook, but as it grows you may find it slow down a little.

Anyway I hope I have given what you need, if not, please do ask, that's what I'm here for, to answer the sort of questions I was asking everyone else 5+ years ago.

Regards, Dai

------------------------------------------Do or do not, there is no try. |------------------------------------------

And it's exactly what i was looking for....thank you!The weird thing is that some values seem to "escape" the cleansing (that If WData < 0.75 * GrNom4 Or WData > 1.75 * GrNom4). And it did not happen before. And some values, although collected, do not appear in the cells. Since the macro runs for some 10 balances simultaneously i'm thinking it's too much for the PC or Excel to handle. Any clues?I am now trying the bit with the 30 random values... it's going to take some time to test it...i will try not to bother you unless really necessary.

How do you mean simultaneously? Separate scales are all feeding into the same workbook at the same time are they?

As you are finding some aberrations (outside the 50-150%) are slipping through and some normal values are being deleted, it would seem likely that this simultaneous running is causing it to delete at the wrong time(or place). What way are you getting these 10 simultaneous balances? (Does the macro pull through 10 values each time, or are you calling the macro 10 times?) Regards, D

------------------------------------------Do or do not, there is no try. |------------------------------------------