Answered by:
VB.net Excel Find Function - Date Month & Year

-
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
Question
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 1, 2017 9:04 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
- Proposed as answer by Cor Ligthert Monday, March 20, 2017 12:07 PM
- Edited by Cor Ligthert Monday, March 20, 2017 12:08 PM
- Unproposed as answer by Cor Ligthert Monday, March 20, 2017 3:15 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
-
-
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 1, 2017 9:04 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 -
-
-
Paul that seems like the most logical alternative. Can you confirm: the Find method does not allow more than one condition?
There is no indication that you can use anything other than a single value. In other words, no expressions.
Michael Downing
Paul ~~~~ Microsoft MVP (Visual Basic)
-
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)
-
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
-
I have found that the Find method will not work for my needs as detailed in the related thread:
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