2

NOTE: My problem was not solved by other similar questions on this as well as other sites. Please have a fair view at my question before judging the same

I am trying to perform a task in which first I have to identify the smallest, 2nd smallest numbers and so on and according to this I have to copy data from one column to another. This will continue until the sum of the copied values becomes grater than or equal to certain value in the sheet (Here row no. for the comparison is given by variable "b"). This will be repeated for 172 different sets which are repeated after every 43 cells.

I have written the following code: 

 Dim m As Range, k As Double, j As Double, b As Double, lIndex As Double, a As Double

 Set m = ActiveSheet.Range("E3:E40")

 For i = 1 To 172
         j = 1
         b = 45 + 43 * (i - 1)
         For k = 1 To 38
                    a = Application.Small(m, j)
                    lIndex = Application.WorksheetFunction.Match(a, m, 0)

                    If Cells(b, 7).Value < Cells(b, 1).Value Then
                        Cells(lIndex, 7).Value = Cells(lIndex, 2).Value
                    Else
                    End If

                    j = j + 1

         Next k

         Set m = m.Offset(43)

 Next i

Now there is an error that pops up saying, Unable to get match property of the worksheet class.

NOTE: I have tried solutions online.

Can there be any other way to do it

OR

Is there something wrong I am doing logically or in the syntax as I am new to excel VBAs and coding itself.

Avi Gupta
  • 57
  • 1
  • 2
  • 9
  • 1
    Possible duplicate of [Excel VBA: Can't get a match, error "Unable to get the Match property of the WorksheetFunction class"](http://stackoverflow.com/questions/17751443/excel-vba-cant-get-a-match-error-unable-to-get-the-match-property-of-the-wor) – BruceWayne May 10 '17 at 04:56
  • No actually I have taken a look at it already. Does'nt solve my problem. – Avi Gupta May 10 '17 at 04:59
  • Also my question is a bit different. I am also open to other methods, if anyone can suggest, for this problem. – Avi Gupta May 10 '17 at 05:00
  • It would help greatly if you could explain *what* doesn't work with other solutions you've tried/found online. Right now it's a pretty vague question. "I have tried solutions online". Great! What solutions did you try, and what didn't work? "Please have a fair review before judging the same." - So far, we only know you're trying to use `Match()` and getting an error. This same error was presented in that possible duplicate. Please expand on what you've tried, how your question is different than others online, and how your issue isn't solved by the top 10 Google results of same question. – BruceWayne May 10 '17 at 05:01
  • @BruceWayne... You have read the question already, I have tried replacing the .Match function with Application. Match, Application.WorksheetFunction.Match and WorksheetFunction.Match... Please read the questio comletely and my code also, you will find that I have already incorporated the points of those solutions without any effect on my error. – Avi Gupta May 10 '17 at 05:05
  • I think you have just read the title and commented. Not expected from a guy with your level of reputation on this site... – Avi Gupta May 10 '17 at 05:07

2 Answers2

4

a = Application.Small(m, j) will surely return an Error Code when j is actually bigger that the size of te range m. In your code, the range m = Range("E3:E40") has 38 cells, but j can go as high as 38 * 172.

Then you try to call Match with an error code as the first parameter a. This resuts in run-time error. Note here that Application.Match would result in an error code while WorksheetFunction.Match raises a run-time error.

In all cases, no error should occur in your Match if you had fetched correctly the "kth smallest" element. Without being able to check all of you code, I guess what you wanted here was 

a = Application.Small(m, k) ' <--- k, not j

And then no error should occur in *.Match(a, m, 0).

A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • 1
    :| you are "stealing" my "bread and butter" :) my precious `Match` ( +1 ) – Shai Rado May 10 '17 at 05:17
  • Thank you so much @A.S.H . It worked like a charm. There is one more question I need to ask if you could help me out... I ran the code and it gives outptut that is not expected. It skipped certain values and copied certain which were not expected. I think this might be because there are multiple values which are same when we use the small function. Do you know any way I can avoid that so that the same values are also processed? – Avi Gupta May 10 '17 at 05:20
  • @AviGupta I have to say that I expected that you will have this problem. Unfortunately, `Match` will always return the first appearence of the searched item. The problem is more complex and is a question on its own. You can probably try some approach with setting *temporarily* a cell to blank after it has been taken. But that should be done carefully (always fecth the smallest, copy and set to blank, loop) ... – A.S.H May 10 '17 at 05:24
  • Will do that, thanks. You are an angel... I am newbie and really need answers like these. Thanks a lot!!! – Avi Gupta May 10 '17 at 05:29
  • 1
    In always wished the error messages would be more descriptive when they were thrown, as the compiler (?) surely knows what part of the function is causing the error. Nice troubleshooting and fix! – BruceWayne May 10 '17 at 05:32
  • @AviGupta is there a reason you unchecked this as accepted answer? – A.S.H May 17 '17 at 22:57
  • @AviGupta ah, it happens. Thanks, glad to help anytime :) – A.S.H May 18 '17 at 17:49
0

After checking your code:

After getting the smallest value, the next value of j should be a + 1 not j + 1.
Why? because if your smallest value is 4 from (4, 6, 10)
on first loop, j = 1, small will return 4.
on second loop, j = 2, small will still return 4, instead of 6.

Paul Co
  • 447
  • 2
  • 9
  • Actually I need the smallest, then 2nd smallest value, then 3rd smallest and so on. So it should proceed like j = 1,2,3... and so on. At least that's the idea. But with the above solution, using k is a better idea instead of j. – Avi Gupta May 10 '17 at 05:26
  • As stated on my answer, if you have values 4, 6, 20, 10. you will always get 4 when j/k will only increment by 1. and you will get 6 when j/k is equal to 5. – Paul Co May 10 '17 at 07:09