• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

array match criteria

Hello. I upload a file about array match some criteria, I already work on some of then like minimum sum value, maximum sum value, but when I tried to create more conditions, then start my problems, I have some mistakes, and I don't know how to handle, reason I am requesting help, please, in the file, at option explicit there are clear information how the code has to work.
thanks .
 

Attachments

  • 01CHANDOOARRAYMATCH.xls
    61.5 KB · Views: 3
Hi Vicktor ,

Can you try your file now and get back on problems ?

Narayan
 

Attachments

  • 01CHANDOOARRAYMATCH.xls
    59.5 KB · Views: 4
NARAYANK991, thanks, you straight up, declaring the variables zone now is ok, so as you said, get back on problems.

As you see in " I " are the total sum for each row and is generate by the code, "j (average), k (range), L (dg diff) and M (ef diff), all this column I just made it up by a function, "SORRY", I am trying to illustrated what I want, and that's why I am asking for help.

I assume the problems you said is J,K,L and M?. (because is not in the code, and is what I am looking for).
"J" is regular average for each row, "K" is called range, is just H minus C;
"L" called DG different, is D minus G ; and "M" called EF different , is F minus E; for each one, there are minimums and maximuns; so the output must shows -"ONLY"- the rows with this conditions.


NARAYANK991 thank you for reading this. see you soon.
 
Hello. I already finish the code, but for some reason a)do not show result, b)do not show me mistakes at all.
you know sometime messages box said error, this time NO messages box. So PLEASE, somebody check for me?, I load again the same file as above but now is COMPLETE.
 

Attachments

  • 01CHANDOOARRAYMATCH.xls
    60.5 KB · Views: 1
Hi Vicktor ,

I do not know from where you have got this code , but in my opinion it may be doing its job ; the problem is the constraints you are placing in the range B26 through B42 , since unless you are positive that the set of numbers in A1 through A19 and the conditions in B26 through B42 are compatible , your result will always be 0 rows.

I have put in a Debug.Print statement so that you can see the intermediate results and decide whether your conditions will ever be fulfilled.

Narayan
 

Attachments

  • 01CHANDOOARRAYMATCH(1).xls
    61 KB · Views: 4
yes, you makes me reconsidering everything, and the conditions about average, middle and borders different don't make any sense, since you pick the total sum, is about simple math to know the average, I mean, since the condition say min= 148 max 152 of course the average will be from 24 to 26 more or less, so I eliminate that condition, the only constraint I really would like to keep it is the middle different (have more sense), I mean ( F - E ) and if the different is not in the range I pick, then do not shows this rows.
thanks; you makes me evaluate everything again . PLEASE, I would like to see your suggestions about this.
 
Hi Vicktor ,

If you can describe exactly what you want done , then probably I will be better able to understand.

1. You will specify numbers in the range A1 through A19 ; are these special numbers ? How do you decide on the specific set of numbers ?

2. You are entering some conditions in the range B26 through B42 ; can you please specify the exact cells you will retain as conditions ? I have seen that the code does not really make use of rangemin , rangemax and a few others. Can you say which ones are significant ?

3. Do you have any real-life example which you have manually worked out , which I can go through and understand ? This is important because to use the macro , we should be sure that it is reliable , and will never output a false result.

Narayan
 
Hi, NARAYANK991. Thanks.
Answer 1). The numbers in the range A1:A19 are not special, and I just write randomly .
2). I retain B26 and B27
B29 and B30
and B41 and B42
You can see the constrains in B26, B27, B29 and B30 are working perfect, when I try to add more filters then I got confused, B29:B30 are max/min and working.
I just need to work in one more condition B41 and B42; is substract the column F minus column E and display the results in M.
answer 3 is about combinatorics, you know all those problems with the same question "how many way you can...do this, or combine this bla,bla. discrete mathematics.

See you soon , I hope.
 
Hi Vicktor ,

Thanks for the clarifications. I'll go through the code and check out what needs to be changed to make it work. Probably in another 4 to 6 hours ( today being a Sunday ! ).

Narayan
 
Hi Vicktor ,

Can you see your file now ? It is not yet complete ; can you go through the output which is appearing in columns C through G , and say whether the numbers which are appearing there are correct ? At present , in any combination , there are repetitions ; is this allowed ?

Narayan
 

Attachments

  • 01CHANDOOARRAYMATCH(1).xls
    94.5 KB · Views: 2
Hi Vicktor ,

Let me understand this :

1. There should not be any repetitions in columns C through G. This I understand.

2. No decimal results. This I do not understand ; as far as I can see , the decimal results are in column J , and this is because the VBA code is as follows :

Range("J" & lRow) = Cells(lRow, "I") / 6 ' average with decimals

When you say avoid decimal results , can you explain ?

3. When you say the results are not according to primes and evens , can you explain ? Which column should contain primes , and which column should contain evens ?

Narayan
 
Back
Top