excel vba date search function

Question

I am a total newbie to excel vba, and I am currently stuck on this problem. Hopefully someone can point me in the right direction.

I am searching a range of cells starting at the last day of the month. For example:

varEOM = "31"
varMM = "03"
varYYYY = "2006"

If there is no data for the 31st of March 2006, then it will not be in the list/column. Therefore, I want to programmatically look back to the 30th, 29th and so forth. I am looking back at 10 year's worth of data.

Also, due to the nature of the data, varYYYY, varMM and varEOM are strings rather than Integers, so I think I need to convert them from Strings to Integers at some point.

I am having issues finding a logical way to loop through and decrement a counter until I hit a valid date.

Here is the code I have so far:

Dim varYYYY As String, varMM As String, varEOM As String, varInteger As Integer, varString As String

varEOM = "31"
varMM = "03"
varYYYY = "2006"

varInteger = CInt(varEOM)
varValueToFind = varYYYY & varMM & varEOM

Do Until Not IsNull(el)

    Set rng = Range("b1", Range("b65536").End(xlUp))
    ' Find the cell reference on the varValuetoFind
    Set el = rng.Find(varValueToFind, LookIn:=xlValues)
    Set fl = rng.Find(varValueToFind, LookAt:=xlPart)
    Debug.Print varYYYY
    Debug.Print varMM
    Debug.Print varEOM
    Debug.Print el
    Debug.Print fl.Address
    Debug.Print varValueToFind

    varInteger = varInteger - 1

    Debug.Print varInteger

 Loop

If there is data on the 31st then the code works. But if there is no data on the 31st and I need to revert to finding 20060330, then the code breaks down.

Thanks in advance. Any help would be appreciated.

Side note: I recommend never to use `Integer` unless you need to communicate with old APIs. Instead always use `Long` and `CLng()`. Read [here](https://stackoverflow.com/a/26409520/3219613) for more information and why. — Pᴇʜ, Jun 09 '17 at 12:49

Luuklag · Answer 1 · 2017-06-09T12:45:37.257

You never update your value to find as it is not inside your Do loop. I moved it inside the loop, so this should work now.

Dim varYYYY As String, varMM As String, varEOM As String, varInteger As Integer, varString As String

varEOM = "31"
varMM = "03"
varYYYY = "2006"

varInteger = CInt(varEOM)


Do Until Not IsNull(el)

varValueToFind = varYYYY & varMM & varInteger 'Here your value to find gets updated.

Set rng = Range("b1", Range("b65536").End(xlUp))
' Find the cell reference on the varValuetoFind
Set el = rng.Find(varValueToFind, LookIn:=xlValues)
Set fl = rng.Find(varValueToFind, LookAt:=xlPart)
Debug.Print varYYYY
Debug.Print varMM
Debug.Print varEOM
Debug.Print el
Debug.Print fl.Address
Debug.Print varValueToFind

varInteger = varInteger - 1

Debug.Print varInteger

Loop

ehm good catch but this still doesn't change the `varValueToFind` during each loop iteration because the incrementing variable is `varInteger` but this is not used for `varValueToFind` so it should be something like this at least: `varValueToFind = varYYYY & varMM & varInteger` otherwise it still searches for the same date in each loop iteration. — Pᴇʜ, Jun 09 '17 at 12:43

xidgel · Answer 2 · 2017-06-12T03:38:42.547

You can make the loop either a Long or a Date and convert to a String for the Find. For instance:

Dim varYYYY As String, varMM As String, varEOM As String, varInteger As Integer, varString As String

dim dayOfMonth as long    ' This is the loop variable

varEOM = "31"
varMM = "03"
varYYYY = "2006"

varInteger = CInt(varEOM)
' Move varValueToFind inside the loop
' varValueToFind = varYYYY & varMM & varEOM

' Change from Do..Loop to For..Next
' Do Until Not IsNull(el)
For dayOfMonth = CLng(varEOM) to 1 Step -1

    ' You can probably move Set rng outside the loop
    ' since this doesn't change
    Set rng = Range("b1", Range("b65536").End(xlUp))

    ' Update the date ...
    varValueToFind = varYYYY & varMM & Format(dayOfMonth , "00")
    ' Find the cell reference on the varValuetoFind
    ' NOT SURE WHY YOU HAVE TWO FINDS HERE
    ' Do you want this instead?
    ' Set el = rng.Find(varValueToFind, LookIn:=xlValues, LookAt:=xlPart)
    Set el = rng.Find(varValueToFind, LookIn:=xlValues)
    Set fl = rng.Find(varValueToFind, LookAt:=xlPart)
    Debug.Print varYYYY
    Debug.Print varMM
    Debug.Print varEOM
    Debug.Print el
    Debug.Print fl.Address
    Debug.Print varValueToFind

    varInteger = varInteger - 1

    Debug.Print varInteger

    ' Exit if find was successful
    ' If Not IsNull(el) Then Exit For
    If Not el Is Nothing Then Exit For
 ' Loop
Next dayOfMonth

Hope that helps

As a test, I added 20060331 to column B. Then I uncommented Debug.Print el and Debug.Print el.address. The script ran successfully. This is the immediate output. 2006 03 31 20060331 20060331 $B$7 — JiggidyJoe, Jun 10 '17 at 01:42
I am guessing that if this statement did not find a correct value "Set el = rng.Find(varValueToFind, LookIn:=xlValues, LookAt:=xlPart)". Then the el variable is set to something other than null? Hence the 91 error? — JiggidyJoe, Jun 10 '17 at 01:49
As per https://msdn.microsoft.com/en-us/library/office/ff839746.aspx. The Range.Find method returns nothing if no match is found. Maybe this statement is not working as expected? "If Not IsNull(el) Then Exit For" — JiggidyJoe, Jun 10 '17 at 01:53
OK. The correct test is `Not el Is Nothing`. I changed it in the code above. In VBA `Null` and `Nothing` are different. — xidgel, Jun 12 '17 at 03:40

excel vba date search function

2 Answers2