Solved: Finding a Date in a Range

[Opv]

The following Excel formula uses defined ranges, findInRng and findWhat. How would this function be converted to work in VBA? My objective is to find the Row number for the cell in which findWhat is located.

=Index(findInRng,Match(findWhat, findInRng, 1))

[Opv]

The following Excel formula uses defined ranges, findInRng and findWhat. How would this function be converted to work in VBA? My objective is to find the Row number for the cell in which findWhat is located.

=Index(findInRng,Match(findWhat, findInRng, 1))

I think I figured it out, if the following is correct:

x = Application.Index(findInRng,Application.Match(findWhat, findInRng,1))

I noticed my worksheet significantly bogged down after applying this formula. Is there something inherent in the Index/Match functions that would be less efficient than the find function?

[Teeroy]

I've been told the .Find method is more efficient in VBA and preferable so that's what I usually use.

From your example I assume findWhat is a single cell Range.


Dim rng1 As Range
Dim rng2 As Range
Dim found As Range
Dim rowNo As Integer

Set rng1 = ThisWorkbook.Names("FindInRng").RefersToRange
Set rng2 = ThisWorkbook.Names("findWhat").RefersToRange

Set found = rng1.Find(What:=rng2.Value, MatchCase:=False)
rowNo = found.Row

[Opv]

I've been told the .Find method is more efficient in VBA and preferable so that's what I usually use.

From your example I assume findWhat is a single cell Range.

Code:

Dim rng1 As Range
Dim rng2 As Range
Dim found As Range
Dim rowNo As Integer

Set rng1 = ThisWorkbook.Names("FindInRng").RefersToRange
Set rng2 = ThisWorkbook.Names("findWhat").RefersToRange

Set found = rng1.Find(What:=rng2.Value, MatchCase:=False)
rowNo = found.Row


Thanks. I'm not getting that to work. The ranges described in my original reply are not that type of range name. They are variables created in the script and set/defined accordingly, as follows:


dim findInRng, findWhat as Range

set findInRng = Sheets("TESTING").Range("A3", Range("A3").End(xlDown).Offset(-1,0))

set findWhat = Sheets("TESTING").Range("A3").end(xlDown)

[Opv]

Actually, I think the Match function would serve my purposes and would certainly simply the function. Possibly something like the following:


Dim findInRng, findWhat, found As Range Dim x as Integer
Set findInRng = Sheets("TESTING").Range("A3", Range("A3").End(xlDown).Offset(-1,0))
Set findWhat = Sheets("TESTING").Range("A3").end(xlDown)
Set found = Application.Match(findWhat, findInRng, 1)

x = found.row

MsgBox x


I tried that but I'm receiving an Object Required error. For what it's worth both findWhat and findInRng represent dates.

[Teeroy]

Thanks. I'm not getting that to work. The ranges described in my original reply are not that type of range name. They are variables created in the script and set/defined accordingly, as follows:


dim findInRng, findWhat as Range

set findInRng = Sheets("TESTING").Range("A3", Range("A3").End(xlDown).Offset(-1,0))

set findWhat = Sheets("TESTING").Range("A3").end(xlDown)



OK. I see now you're trying to match the last row in a column with anything above it in the column.

Also you have range variables with the names you mentioned where I thought you meant named ranges in the worksheet. :banghead:

That makes things easier. Try:


Dim FindInRng As Range
Dim findWhat As Range
Dim found As Range
Dim rowNo As Integer

Set FindInRng = Sheets("TESTING").Range("A3", Range("A3").End(xlDown).Offset(-1, 0))
Set findWhat = Sheets("TESTING").Range("A3").End(xlDown)
Set found = FindInRng.Find(What:=findWhat.Value, MatchCase:=False)
rowNo = found.Row

[Teeroy]

Actually, I think the Match function would serve my purposes and would certainly simply the function. Possibly something like the following:


Dim findInRng, findWhat, found As Range Dim x as Integer
Set findInRng = Sheets("TESTING").Range("A3", Range("A3").End(xlDown).Offset(-1,0))
Set findWhat = Sheets("TESTING").Range("A3").end(xlDown)
Set found = Application.Match(findWhat, findInRng, 1)

x = found.row

MsgBox x


I tried that but I'm receiving an Object Required error. For what it's worth both findWhat and findInRng represent dates.

findWhat is a range object and you need to send a value to be tested. Your match may work if you change findWhat to findWhat.value.

[Opv]

findWhat is a range object and you need to send a value to be tested. Your match may work if you change findWhat to findWhat.value.

Thanks. I'm still receiving an Object Required error. I'm wondering if the fact that I'm dealing with dates has anything to do with the error.

[Teeroy]

Thanks. I'm still receiving an Object Required error. I'm wondering if the fact that I'm dealing with dates has anything to do with the error.

Match returns an integer not a range object and it's value is a row relative to the search array, now an absolute row number relative to the worksheet. Even allowing for this I couldn't get Match to work correctly and still recommend using .Find which works for me even with dates.

[Opv]

Match returns an integer not a range object and it's value is a row relative to the search array, now an absolute row number relative to the worksheet. Even allowing for this I couldn't get Match to work correctly and still recommend using .Find which works for me even with dates.

Thanks. That is helpful. My reason for considering moving away from Find and considering Match is that I wasn't able to get Find to work if the date in findWhat does not exist in findInRng, in which case my Find statement was receiving an error.

Is there a way to get Find to emulate Match if the exact date does not exist in the range to be searched, for example, emulating a Match Type of 1?

[Teeroy]

If a date isn't found in the range then the Range object "found" doesn't exist. You can test for this with a simple if statement as per below:

If found Is Nothing Then
' Do something if not found such as raise a message and exit the sub
End if

or conversely

If found Is Not Nothing Then
' Do something if the date is found
End if

[Opv]

If a date isn't found in the range then the Range object "found" doesn't exist. You can test for this with a simple if statement as per below:

If found Is Nothing Then
' Do something if not found such as raise a message and exit the sub
End if

or conversely

If found Is Not Nothing Then
' Do something if the date is found
End if

Thanks, but it is not my objective just to know if the date exists. What I want to achieve is to either find the exact date OR the next closest date that is smaller than the exact date, like is achieved with the Match function.

[Teeroy]

OK. I found two fixes that could make your Match function work.

Set found = Application.WorksheetFunction.Match(findWhat.Value, findInRng, 1)

For Match to work as you want it the dates need to be in ascending order otherwise you'll get an incorrect answer (probably last row in findInRng).

If the dates are not ascending and you can't re-order them you could copy the range elsewhere, sort it and get the Match then use the value in the Match location in a .Find method in the original Range.

[Opv]

OK. I found two fixes that could make your Match function work.

Set found = Application.WorksheetFunction.Match(findWhat.Value, findInRng, 1)

For Match to work as you want it the dates need to be in ascending order otherwise you'll get an incorrect answer (probably last row in findInRng).

If the dates are not ascending and you can't re-order them you could copy the range elsewhere, sort it and get the Match then use the value in the Match location in a .Find method in the original Range.

I'm still getting the Object required error. I've read that Match just don't like dates. Some of the functions I've seen to get around the problem looked to be more convoluted than it's worth for my purposes. Interestingly, combining Index and Match works fine with dates. I don't understand how that could be with Match causing such a problem with dates. At any rate, I think I'm going to stick with Index-Match. At least it's working and it's not bogging my system down like it was initially. think that my initial problem with the code being sluggish was due to experiencing low system resources at that particular time. It seems to be working fine now. Thanks.