VB.net Excel Find Function - Date Month & Year

Question

Hi, using the Excel Find function in VB.net, I want to find the first instance of a cell with a value containing both a desired month and year within a range of date values.

For example, with the following code I am seeking the first instance of a date within April 2016:

Dim ResField30B As String
ResField30B = CDate("04/01/2016")
Range01_01 = Worksheet01_01.Range("C1:C500")
Range01_02 = Range01_01.Find(Month(ResField30B), Year(ResField30B))

This code returns a "Type Mismatch" error.  The code with just the Month qualifier works fine.  Any suggestions?  Thanks. 

---

Michael Downing

Answer 1

If you have option strict on, your code would not even go in Visual Studio. 

Resfield is a string and therefore you cannot make it implecitly a DateTime. 

Be aware with also option Infer On the code

Dim ResField30B = CDate("04/01/2016") would direct go for that part. Option Infer On would direct make first a DateTime field from ResField behind the scene and use that also at runtime. 

---

Success
Cor

Answer 2

Thanks for the response Cor.

Under the VS path -

[Tools > Options > Projects and Solutions > VB Defaults]

The settings when I first ran the program were Option Strict Off,  Option Infer On.  I set Option Infer to Off and got the same error message.

---

Michael Downing

Answer 3

Michael,

Did you also change the code in the way I wrote?

---

Success
Cor

Answer 4

Your search is too complicated for the Find method. One alternative is to loop through the Cells in the Range:

    Dim SearchDate As Date
    SearchDate = #4/1/2016#

    For Each c In Worksheets("SheetName").Range("C1:C500").Cells
        If Month(c.Value) = Month(SearchDate) And Year(c.Value) = Year(c.Value) Then
            '...do whatever
            Exit For
        End If
    Next

Note that if the first row of the Worksheet contains headers you will want to start your Range with C2.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 5

Cor yes I did - originally it was:

Dim ResField30B As String

And I changed it to

Dim ResField30B AsDateTime

Both produced the same "Type Mismatch" error.

---

Michael Downing

Answer 6

Difficult if you show something different from what I wrote.

Did you do?

dim ResField30B as DateTime = CDate("04/01/2016")

This should go in General World format and US format but not in ISO format. 

Therefore if you live in for instance Japan or China this fails. 

In the way I show it now you can use it even with all options Off

---

Success
Cor

Answer 7

Paul that seems like the most logical alternative.  Can you confirm: the Find method does not allow more than one condition?

---

Michael Downing

Answer 8

Paul and an additional question - how does processing time (roughly) compare between Find and iterating though the range using a loop?  I ask because I work with very large data files.  Thanks.

---

Michael Downing

Answer 9

Paul that seems like the most logical alternative.  Can you confirm: the Find method does not allow more than one condition?

---

Michael Downing

There is no indication that you can use anything other than a single value. In other words, no expressions. 

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 10

Paul and an additional question - how does processing time (roughly) compare between Find and iterating though the range using a loop?  I ask because I work with very large data files.  Thanks.

---

Michael Downing

I can't answer that because I don't know how many rows you are searching through. Actually it should be fairly easy for you to test since you already have the Workbooks. But like I said, Find will not work on a partial date.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 11

Testing has found that iterating though a range of 500,000 records by loop takes 1 1/2 hours, which is unacceptable.  The Find method is nearly instantaneous.

I experimented with a two-stage Find process - first locate the desired year in a sorted range, then modify the search range and locate the desired month.  This however produced incorrect results.  I am posting this problem in another thread and will return to complete this thread when the problem is resolved.  Thanks.

---

Michael Downing

Answer 12

I have found that the Find method will not work for my needs as detailed in the related thread:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/5b99ee06-3f85-482f-9f64-aa86a0323bd8/vbnet-excel-find-method-incorrect-results?forum=vbgeneral

And I will be using a looping methodology as the solution as suggested by Paul even though processing times are long.  Thanks, this concludes the thread.

---

Michael Downing