In a recent post, I was asking for advice about the fastest method to find values in a range using VBA, and came across sites that indicated the Range.Find method was hundreds of times faster than looping. So I gave it a go, and it works fine for me using the below code on integers. It does not work on time however, which is what I do need. There seems to be scant data on using this method for searching times, and some data regarding dates. Does anyone have any thoughts on this?
The attached workbook has the below code and data in Sheet 1 as per the screen shot for testing.
If you modify the code below from ".Range("f6:f10")" to ".Range("c6:c10"), and "myRangeTimes" to "myRangeIntegers", then you will be able to witness the Find method working with the corresponding integer values 1-5 in Sheet1. Using this same code on the time values as is shown below, returns the wrong result from the Find, setting the user entry "12:00:00 AM" cell interior color with the result from "10:00:00 AM" in the myRangeTimes range. I suspected the format had something to do with this, so I copied and pasted the user input cells to the myRangeTimes range to ensure there was no differences, but the problem persists.
Sub colorInteriorByFindResult()
Dim rngTimes As Range
Dim findResult As Range
Set rngTimes = Worksheets("Sheet1").Range("f6:f10") 'This is the range of possible cells containing times to format the interior colors
For Each cell In rngTimes
Set findResult = Worksheets("Sheet1").Range("myRangeTimes").Find(cell)
If findResult Is Nothing Then
' Do nothing
Else
cell.Interior.Color = findResult.Interior.Color
End If
Next
End Sub
Thanks in advance for any advice folks,
Frank
Bookmarks