Results 1 to 7 of 7

.Find method usage on Time Values advice?

Threaded View

  1. #1
    Registered User
    Join Date
    08-08-2020
    Location
    Boston
    MS-Off Ver
    2019
    Posts
    33

    .Find method usage on Time Values advice?

    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
    Attached Images Attached Images
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Advice - Method to use in transferring data from one workbook to another
    By CraigsWorld in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-24-2018, 12:57 AM
  2. Finding the last row on a worksheet - advice on best method
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2014, 09:10 PM
  3. Run-time error 91 using cells.find method
    By WeirnetherlandsBart in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-03-2012, 06:40 AM
  4. Sortin method advice please
    By Zipadeedoodaa in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2006, 05:00 PM
  5. [SOLVED] Is there a method to find distinct values in a column
    By Ivan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-22-2005, 12:55 PM
  6. [SOLVED] Advice on method
    By Tempy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-29-2005, 01:05 PM
  7. Find method - finding multiple values
    By nathan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-23-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1