none
VB.net Excel Find Method – Incorrect Results

    Question

  • Hi, the goal is to find the first instance of a date in an Excel range with a desired month and year.

    Range A1:A10 is as follows:

    1/1/2015
    1/1/2015
    1/1/2015
    4/1/2016
    4/1/2016
    4/1/2016
    6/1/2016
    7/1/2016
    7/1/2016
    7/1/2016
    

    Code:

    Dim Range01_01A As Microsoft.Office.Interop.Excel.Range
    Dim Range01_01B As Microsoft.Office.Interop.Excel.Range
    Dim Range01_01C As Microsoft.Office.Interop.Excel.Range
    Dim Range01_01D As Microsoft.Office.Interop.Excel.Range
    
    Dim Date01_01 As Date
    Dim Row01_01A As Integer
    Dim Row01_01B As Integer
    
    Date01_01 = #1/1/2015# ‘Or date 6/1/2016
    Range01_01A = Worksheet01_01.Range("A1:A10")
    Range01_01B = Range01_01A.Find(Year(Date01_01))
    Row01_01A = Range01_01B.Row
    Range01_01C = Worksheet01_01.Range("A" & Row01_01A.ToString & ":A10") ‘Range modified based on first Find
    Range01_01D = Range01_01C.Find(Month(Date01_01))
    Row01_01B = Range01_01D.Row
    

    Row01_01A should return the row of the first instance of the year from Date01_01, and Row01_01B should return the row of the first instance of the month from Date01_01 within the modified range.

    For date 1/1/2015, the results should be Row01_01A = 1, Row01_01B = 1; however, the actual results are 2 and 3, respectively.

    For date 6/1/2016, the results should be Row01_01A = 4, Row01_01B = 7; however, the actual results are 4 and 7, respectively.

    Any suggestions as to the problem?  I am using Find rather than a looping technique because the actual data files are very large and the processing time with looping is too long.  Thanks.



    Michael Downing

    Saturday, April 01, 2017 5:42 PM

Answers

  • Sorry - on my original post, the following sentence:

    For date 6/1/2016, the results should be Row01_01A = 4, Row01_01B = 7; however, the actual results are 4 and 7, respectively

    Should have read:

    For date 6/1/2016, the results should be Row01_01A = 4, Row01_01B = 7; however, the actual results are 4 and 5, respectively

    Thanks.


    Michael Downing

    There are a couple of issues here. First, the Find method searches after the first cell in the upper left corner of the Range, which means that cell is skipped. In your example it's A1, so you would want to check that cell first for the year and month values before using Find.

    Second, and probably the larger issue for your code is that Find will look anywhere in the cell for the value your specify. For Month(6/1/2016) which is 6, Find will match any cell containing a 6, including 4/1/2016. That is why the code examples I have provided so far are rather specific in searching for the year part of the date and then the month part of the date.

    If the day was always the same (e.g. first day of month) then using Find would be easy, but I suspect that in your case it is not.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Oakwinds Monday, May 01, 2017 8:57 PM
    Monday, April 10, 2017 1:23 PM

All replies

  • If you want to use Find then you will have to find the first instance that matches the year value, then use the Month function and check the value of that Cell to see if it's the month you are looking for. If it is not then you will need to do another Find and specify the After argument to indicate that you want to start the search after the current Cell. This process will be repeated until your find a match for both the year and month.

    I don't now that this is any faster than what I suggested in your other thread but you can give it a try.

    Addendum: I should have mentioned that you can also use FindNext to repeat your search.


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Saturday, April 01, 2017 8:38 PM
  • Instead of VBA you can do this in separate column

    Say column A had rows with text and some rows numeric and also some rows with date.

    For column B you add this formula: =AND(ISNUMNBER(A1),A1>NOW()-3650,A1<=NOW()+3650)

    This will give you date value as numeric within 10 yers (3650 days)

    Copy the function all the way in B column. On the last cell in column B use MIN(B1:{yourlastcell}) function to find the MIN value which is your first date. 

    Saturday, April 01, 2017 11:06 PM
  • Paul:

    [1] Can you give me a code example of using an After modifier with the Find method?

    [2] When I use FindNext, I get the error "FindNext method of Range class failed".

    Goranco:

    [1] I am using VB.net, not VBA.

    [2] The data files I work with contain hundreds of thousands of records, so I want to avoid inserting Excel formulas for each record.

    Thanks.


    Michael Downing

    Sunday, April 02, 2017 7:45 PM
  • Paul:

    [1] Can you give me a code example of using an After modifier with the Find method?

    Thanks.


    Michael Downing

    This worked for me. You will need to change the date criteria and Range for your application. I don't know how fast it will be given the number of rows you are working with.

        Public Sub ExcelFind()
    
            Dim excelApplication As New Excel.Application
            Dim excelWorkbook As Excel.Workbook
            Dim excelWorksheet As Excel.Worksheet
            Dim excelRange As Excel.Range
            Dim excelWorkbooks As Excel.Workbooks = excelApplication.Workbooks
    
            excelWorkbooks = excelApplication.Workbooks
            excelWorkbook = excelWorkbooks.Open("C:\Users\...\Documents\My Database\Excel\Book2007.xlsx")
            excelWorksheet = excelWorkbook.Sheets("Sheet5")
            Dim date01_01 As Date = #2/1/1996#
            Dim dateMonth As Integer = Microsoft.VisualBasic.Month(date01_01)
            Dim dateYear As Integer = Microsoft.VisualBasic.Year(date01_01)
            With excelWorksheet.Range("F1:F831")
                excelRange = .Find(dateYear)
                If excelRange Is Nothing Then
                    Console.WriteLine("Date not found")
                Else
                    Do
                        If Microsoft.VisualBasic.Month(excelRange.Value) = dateMonth Then
                            Console.WriteLine(excelRange.Value.ToString)
                            Exit Do
                        Else
                            excelRange = .Find(dateYear, excelRange)
                        End If
                    Loop Until excelRange Is Nothing
                End If
            End With
    
            excelRange = Nothing
            excelWorksheet = Nothing
            excelWorkbook.Close()
            excelWorkbook = Nothing
            excelWorkbooks = Nothing
            excelApplication.Quit()
            excelApplication = Nothing
    
        End Sub


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, April 03, 2017 4:19 PM
  • Paul thanks for the suggestion, but yes - including a looping step makes the process too long when using large data files.

    Does anyone have a suggestion as to why my original code (the two-step Find process) produced incorrect results?  If it worked correctly it would produce instantaneous results and be the perfect solution.  Thanks.


    Michael Downing

    Saturday, April 08, 2017 6:26 PM
  • Sorry - on my original post, the following sentence:

    For date 6/1/2016, the results should be Row01_01A = 4, Row01_01B = 7; however, the actual results are 4 and 7, respectively

    Should have read:

    For date 6/1/2016, the results should be Row01_01A = 4, Row01_01B = 7; however, the actual results are 4 and 5, respectively

    Thanks.


    Michael Downing

    Sunday, April 09, 2017 7:10 PM
  • Sorry - on my original post, the following sentence:

    For date 6/1/2016, the results should be Row01_01A = 4, Row01_01B = 7; however, the actual results are 4 and 7, respectively

    Should have read:

    For date 6/1/2016, the results should be Row01_01A = 4, Row01_01B = 7; however, the actual results are 4 and 5, respectively

    Thanks.


    Michael Downing

    There are a couple of issues here. First, the Find method searches after the first cell in the upper left corner of the Range, which means that cell is skipped. In your example it's A1, so you would want to check that cell first for the year and month values before using Find.

    Second, and probably the larger issue for your code is that Find will look anywhere in the cell for the value your specify. For Month(6/1/2016) which is 6, Find will match any cell containing a 6, including 4/1/2016. That is why the code examples I have provided so far are rather specific in searching for the year part of the date and then the month part of the date.

    If the day was always the same (e.g. first day of month) then using Find would be easy, but I suspect that in your case it is not.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Oakwinds Monday, May 01, 2017 8:57 PM
    Monday, April 10, 2017 1:23 PM
  • Paul thanks for the suggestion, but yes - including a looping step makes the process too long when using large data files.

    You could also try speeding this up a bit by dumping the Range into an Array:

            Date01_01 = #7/1/2016# 'Or date 6/1/2016
            Dim dateMonth As Integer = Microsoft.VisualBasic.Month(Date01_01)
            Dim dateYear As Integer = Microsoft.VisualBasic.Year(Date01_01)
            Dim excelRangeArray As Object(,)
            excelRangeArray = excelWorksheet.Range("A1:A10").Value
            Dim rowNumber As Integer = 0
    
            For Each excelCell As Object In excelRangeArray
                If Microsoft.VisualBasic.Year(excelCell) = dateYear And Microsoft.VisualBasic.Month(excelCell) = dateMonth Then
                    MsgBox("Row found: " & rowNumber + 1)
                    Exit For
                Else
                    rowNumber += 1
                End If
            Next excelCell


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, April 10, 2017 2:05 PM
  • Hi Oakwinds,

    Have you solved your issue now? If yes, please share your result here or remember to close your thread by marking helpful post as answer, it is very beneficial to the other communities who face the same issue.

    Best regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 20, 2017 6:43 AM
    Moderator
  • Hi all – sorry for the delay in responding.  After experimentation, I found that the Find method will not solve my problem due to the limitations of Find as described by Paul on his posting of Monday, April 10, 2017 1:23 PM.

    Rather I am going to use a looping methodology, even though the processing time can exceed an hour for large data files.

    This thread is a corollary of the following thread:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/b1292c93-12bf-4b93-9ea2-0c005717dfe3/vbnet-excel-find-function-date-month-year?forum=vbgeneral

    And I am closing that thread now.  Thanks.


    Michael Downing

    Monday, May 01, 2017 8:57 PM