Databases
StackOverflow2013
Postsdbo
POVBA, MATCH function does not returns the correct value
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA, MATCH function does not returns the correct value
    primarykey
    data
    text
    <p>I have a problem with calling the Match function from a VBA code: it returns the wrong values. I have a speadsheet like this (it's an example):</p> <pre><code> a b c d 1 testRange testValue 2 01/01/2011 00:00 03/01/2011 00:00 testValue = A4 3 02/01/2011 00:00 4 03/01/2011 00:00 result from match function 5 04/01/2011 00:00 3 testResult = MATCH(C2,A2:A20,1) 6 05/01/2011 00:00 7 06/01/2011 00:00 testResult 8 07/01/2011 00:00 9 9 08/01/2011 00:00 10 09/01/2011 00:00 11 10/01/2011 00:00 12 11/01/2011 00:00 13 12/01/2011 00:00 14 13/01/2011 00:00 15 14/01/2011 00:00 16 15/01/2011 00:00 17 16/01/2011 00:00 18 17/01/2011 00:00 19 18/01/2011 00:00 20 19/01/2011 00:00 </code></pre> <p>the range that I'm searching is in A1:A20, and I'm looking for the value wrote in C2, that as you can see is just a value of the range (this to be sure that the value is in the range). I expect the Match function to return the number 3, like it does when called from the spreadsheet (cell C5), but the function called in VBA returns the value 9 (cell C10). I used this code:</p> <pre><code>Sub testMatch() testRange = Range("A2:A20") testValue = Range("C2") testResult = Application.WorksheetFunction.Match(testValue, testRange, 1) Range("C8") = testResult End Sub </code></pre> <p>I have already tried to cast testValue as Date and as Double (solutions suggested in other forums), but with Date I have the same result and with Double I get the Error 1004 "Unable to get the Match property of the WorksheetFunctions class"</p> <p>Any suggestion?</p> <p>Thanks</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndrew Strathclyde
    1. USAndrew Strathclyde
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POVBA, MATCH function does not returns the correct value
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POVBA, MATCH function does not returns the correct value
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs there a reason that you are using a different lookup range in your vba code than you used on your worksheet?
      singulars
      1. POVBA, MATCH function does not returns the correct value
      1. USGSTD
    2. COpardon, i fixed the range reference, it was due to the creation of the example, but the problem is still there
      singulars
      1. POVBA, MATCH function does not returns the correct value
      1. USAndrew Strathclyde
    3. COExcel does not play well with dates that are formatted as human-readable dates. If the format of your lookup range and lookup value are not absolutely identical, you will gets some strange results. Even if they look the same on the spreadsheet, they may be different. For example Jan 3, 2011 and March 1, 2011 can look identical depending on the formatting of your cells. (day/month/year or month/day/year)
      singulars
      1. POVBA, MATCH function does not returns the correct value
      1. USGSTD
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload