0
Sub macro18()
    Sheets("May2019").Cells(2, "N").Value = Application.Match(Sheets("May2019").Cells(2, "B").Value, Sheets("Futures_Data").Range("$B$2:$B$730"), 0)
    Sheets("May2019").Cells(2, "O").Value = Application.Match(Sheets("May2019").Cells(2, "C").Value, Sheets("Futures_Data").Range("$C$2:$C$730"), 0)
End sub

  • I am getting #N/A, when I run the above code in marco

  • I am getting the immediate row number when use the following functions when match found. Is there any wrong with the above VBA. I am checking the dates with same format.

    =MATCH(B2,Futures_Data!$B$2:$B$730,0)
=MATCH(C2,Futures_Data!$C$2:$C$730,0)
JvdV
  • 70,606
  • 8
  • 39
  • 70
vaasusk
  • 17
  • 3
  • 3
    Should work if you are not making any mistakes with sheetnames or workbooks. Maybe it helps to first qualify a workbook, then worksheets, then ranges... *Sidenote*: You have more questions asked, though no accepted answers. It would be appreciated you revisit these older questions of yours and accept those answers that helped you out. It's simply how this site works =). Take the sites tour if you are unfamiliar. – JvdV Apr 21 '20 at 10:59
  • What are you talking about? `Application.Worksheetfunction.Match()` exists and does the same as the `=Match()` Excel function, but `Application.Match()`, what is that? – Dominique Apr 21 '20 at 12:45
  • @Dominique - appropriate usage https://stackoverflow.com/questions/27302794/application-match-gives-type-mismatch – SJR Apr 21 '20 at 12:48
  • @SJR: Application.Match(), is this language dependent? I tried it on my Dutch Excel and it seems not to exist (hence my comment). – Dominique Apr 21 '20 at 12:49
  • @Dominique - not as far as I know. Do you mean it errors in the VBE? – SJR Apr 21 '20 at 12:51
  • @SJR: I mean, when I type `Application.` (with the dot at the end), the listbox which appears does not show the `Match()` function, it goes from `MapPaperSize` to `Mathcoprocessoravailable`. – Dominique Apr 21 '20 at 12:56
  • @Dominique - in fact it's the same for me, in English, which I'd never noticed but you should still be able to use. – SJR Apr 21 '20 at 12:59
  • 1
    @SJR: Hey, we just found in bug in MS-Excel :-) – Dominique Apr 21 '20 at 13:00
  • @Dominique - is there a prize? I wonder why that happened but must be longstanding. – SJR Apr 21 '20 at 13:02
  • @SJR: do you know how to issue a bugreport for Excel? I've been to the Microsoft community fora, but I'm being thrown from one side to the other so much, my head is spinning :-) – Dominique Apr 21 '20 at 14:54
  • @Dominique see [here](https://stackoverflow.com/q/59212585/9758194), for some additional background =) – JvdV Apr 21 '20 at 15:12
  • @JvdV: sorry, but your link explains why `Application.WorksheetFunction.Match()` is the same as `WorksheetFunction.Match()`, while this question is about `Application.Match()` (without the `WorksheetFunction)`. – Dominique Apr 21 '20 at 15:16
  • @Dominique, just to show you that both option 1 and 3 will use intellisense and that using `Application.Match` will loose intellisense. And to my understanding that is **not** a bug. Simply because you have not descended into the tree up to the point where intellisense knows what worksheetfunctions to return. – JvdV Apr 21 '20 at 15:20
  • And MS even uses `Application.Match` in their own example of the `WorksheetFunction.Match` [documentation](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.match) =) – JvdV Apr 21 '20 at 15:27

0 Answers0