none
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

    Monday, March 20, 2017 8:58 AM

Answers

  • 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)

    • Marked as answer by Oakwinds Monday, May 01, 2017 9:04 PM
    Tuesday, March 21, 2017 12:27 PM

All replies

  • 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



    Monday, March 20, 2017 12:07 PM
  • 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

    Tuesday, March 21, 2017 8:13 AM
  • Michael,

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


    Success
    Cor

    Tuesday, March 21, 2017 8:44 AM
  • 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)

    • Marked as answer by Oakwinds Monday, May 01, 2017 9:04 PM
    Tuesday, March 21, 2017 12:27 PM
  • 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

    Tuesday, March 21, 2017 12:28 PM
  • 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

    Tuesday, March 21, 2017 3:44 PM
  • Paul that seems like the most logical alternative.  Can you confirm: the Find method does not allow more than one condition?

    Michael Downing

    Wednesday, March 22, 2017 12:57 PM
  • 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

    Wednesday, March 22, 2017 1:46 PM
  • 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)

    Wednesday, March 22, 2017 4:34 PM
  • 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)

    Wednesday, March 22, 2017 4:39 PM
  • 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

    Saturday, April 01, 2017 5:39 PM
  • 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

    Monday, May 01, 2017 9:04 PM