0

I'm using Application.Match and expecting

Application.Match(["Search Term"],["Search Array"],["-1 Optional Argument 
to show the first result higher than 'Search Term'"]

I'm getting a type mis-match error, despite the fact that manually searching that same array will show up a number of examples that I believe should satisfy the script!

For Example: 

Sheets("StockPointsLog").Range("E75").Value = 853.45
ReferenceTargetSell = 850.605

I've tried using '''Application.WorksheetFunction.Match''' to see if this fixes the problems that I'm suffering, but this doesn't seem to be a 100% reliable method, so I would prefer to abandon it in favour of an actual solution.

I've tried encapsulating this line in '''CDbl()''' which simply converts the error code into a number.

I have also tried to manually edit values in the array, in order to confirm that I DEFINITELY have a valid result to show, but to no avail.

As you can see I'm error handling for if there is no result (which for the sake of the rest of my Sub is a valid outcome). Which means that the actual outcome for me is that the .match result comes out to = 0, rather than N/A.

I've been googling madly now for 2 days, and I am having to resort to begging you, my dear smart friends for your help and experience!

i = 2

Do While i <= (TotalTrades + 1)

ReferenceDateRow = Application.Match(ReferenceDate, 
Sheets("StockPointsLog").Range("A1:A1000"), 0)

ReferenceTargetSell = Sheets("Trades").Range("B" & i).Value

On Error Resume Next

NewTradeDate = Application.Match(ReferenceTargetSell, 
Sheets("StockPointsLog").Range("E" & ReferenceDateRow & ":E1001"), -1)
NewTradeDate = Sheets("StockPointsLog").Range("A" & NewTradeDate).Value

ReferenceDateRow comes back with a legitimate and correct value. Happy with that.

ReferenceTargetSell also works as expected.

The issue starts when I'm trying to set NewTradeDate

Philip Day
  • 69
  • 7
  • Hi @BigBen I'm getting Run-time Error 13: Type Missmatch now, on the application.Match line.Worth saying: That at the top of this sub, **Dim NewTradeDate as Double** is the type in use here. – Philip Day Mar 24 '19 at 16:06
  • If you are using `Application.Match` it should be a `Variant`. See [this answer](https://stackoverflow.com/questions/27302794/application-match-gives-type-mismatch). You should then test if no match is found with `IsError`. – BigBen Mar 24 '19 at 16:09
  • BigBen, thank you for responding swiftly once again. I have previously read that answer, I'm not getting there on how that will help me Sir? I have used CDble() around my application.Match line and it clears the error, but I'm getting a figure in the thousands that I cannot explain. I'll edit my question now though to include this fact. – Philip Day Mar 24 '19 at 16:12
  • It explains how to handle the possibility of no match found - note that `On Error Resume Next` in most cases isn't error handling, it just ignores any errors that arise. What is the value of `ReferenceDateRow` when the error occurs? – BigBen Mar 24 '19 at 16:16
  • 1
    Don't use `Cdbl` - it converts the error code to a number! There's still no match and `Cdbl` makes that even less clear. Use `IsError` as the linked answer suggests and `Dim NewTradeDate as Variant`. – BigBen Mar 24 '19 at 16:17
  • ReferenceDateRow = 2 Good point on the CDbl. I think I'm just rushing and getting desperate now, haha. Please could you explain how I can error handle using a Var then, as I'm a little lost. – Philip Day Mar 24 '19 at 16:21
  • 1
    Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/190586/discussion-between-bigben-and-philip-day). – BigBen Mar 24 '19 at 16:21
  • Just realised, the issue here is not the one BigBen and I discussed. There shouldn't have been an error as the value does exist in the array. Can anybody add their 2 pence? – Philip Day Mar 24 '19 at 17:12
  • What are the values of `ReferenceTargetSell` and `ReferenceDateRow` when you get this error? – BigBen Mar 24 '19 at 17:17
  • Shall we go back to chat? ReferenceTargetSell 871.185 and ReferenceDateRow = 2 – Philip Day Mar 24 '19 at 17:21
  • If you use a regular `MATCH` formula on the worksheet, what is the result? – BigBen Mar 24 '19 at 17:26
  • Also comes out as N/A. Which I also don't understand, because I have literally set this now to Match(The Cell that is ReferenceTargetCell, Selected the column with the answer, and parsed -1 as the argument). I have then also set an answer cell within that column to = ReferenceTargetCell + 0.1 So it must work, right? – Philip Day Mar 24 '19 at 17:28
  • 1
    If the function returns an error, then there is no match. Maybe add a screenshot of sample data? Are your values in descending order? – BigBen Mar 24 '19 at 17:30
  • I've worked out the issue here. The column isn't sorted in descending order. It can't be due to the way the sheet works. I'll have to find a workaround. – Philip Day Mar 24 '19 at 17:33

0 Answers0