find values from from range

[breilly00]

Sub finddate()

Dim foundRng As Range
Sheets("download").Activate

Set foundRng = Range("g2:g9").Find(Sheets("schedule").Range("b2").Value)
If Not foundRng Is Nothing Then
MsgBox "Found at " & foundRng.Address
End If
End sub

I am having trouble while comparing a time field when all cells are custom formatted as time 13:30
For example B2 has 17:05, cell g2 in range of g2:g9 also has 17:05 but cannot get a find. 'When I copy and paste value from g2 to b2 here is also no find

I ran a test using numbers instead of times with format as numbers and entered a 1, 2 and 3 in the range and a find value of 1 in b2 also formatted as number and it works. Then changed 1, 2 & 3 to 1.00, 2.00 and 3.00 and the find value to 1.00 and did not work.

What am I doing wrong?

 

[Domenic]

You'll need to convert your search value to a Date type...

Set foundRng = Range("g2:g9").Find([COLOR=#ff0000]CDate[/COLOR](Sheets("schedule").Range("b2").Value))

Hope this helps!

 

[Marcelo Branco]

Being Schedule!B2 = 17:05 this worked for me

Sub finddate()
    Dim foundRng As Range, MyMatch As Variant
    
    Sheets("download").Activate
    MyMatch = Application.Match(Sheets("schedule").Range("B2"), Range("G2:G9"), 0)
    If Not IsError(MyMatch) Then
        Set foundRng = Range("G2").Offset(MyMatch - 1)
        MsgBox "Found at " & foundRng.Address
    End If
End Sub

M.

 

[breilly00]

‘Download’ sheet
Date/Time in column F
Heading in row one and data in row 2 to row 56
Data in column F is below
Date/Time
05-07-2019 10:00 AM
05-07-2019 10:00 AM
05-07-2019 10:00 AM
05-07-2019 10:00 AM
05-07-2019 10:00 AM
05-14-2019 10:00 AM
05-14-2019 10:00 AM
05-14-2019 10:00 AM
05-14-2019 10:00 AM
05-14-2019 10:00 AM


‘Schedule’ sheet Column A
Heading in row 1 and data in row 2
Date/Time
05/07/2019 10:00 AM


Sub frommrexcel()
Dim foundRng As Range, MyMatch As Variant

Sheets("download").Activate
MyMatch = Application.Match(Sheets("schedule").Range("a2"), Range("f2:f56"), 0)
If Not IsError(MyMatch) Then
Set foundRng = Range("G2").Offset(MyMatch - 1)
MsgBox "Found at " & foundRng.Address
End If
End Sub


Running frommrexcel macro returns $G$2 in text box




Sub finddate()
Dim foundRng As Range


Sheets("download").Activate
Set foundRng = Range("f2:f56").Find(CDate(Sheets("schedule").Range("a2").Value))
If Not foundRng Is Nothing Then
MsgBox "Found at " & foundRng.Address
End If




Running finddate macro returns $F$3 in text box






Why doesn’t the value displayed be ($F$2), the first value in the column? Macro ‘frommrexcel’ says the match was found in a different column using the offset (down 1 row and 1 column to the left). Macro ‘finddate’ says the match was found in the third row, and in the same column.


I thought that the search would start at F2 and go down the column until it found the first match. In this case, the return address would be $F$2 – which is what I am looking for.

 

[Marcelo Branco]

Try

Sub frommrexcel()
    Dim foundRng As Range, MyMatch As Variant
    
    Sheets("download").Activate
    MyMatch = Application.Match(Sheets("schedule").Range("a2"), Range("F2:F56"), 0)
    If Not IsError(MyMatch) Then
        Set foundRng = [COLOR=#ff0000]Range("F2")[/COLOR].Offset(MyMatch - 1)
        MsgBox "Found at " & foundRng.Address
    End If
End Sub

M.

 

[breilly00]

sticking my tail between my legs.

I looked all over the macros and missed this.

Thank you so much for both of your replies. Works like I thought I could get it to work. This is why I admire and respect the MrExcel MVP's. They are a blessing to all us new-bees.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[Domenic]

I thought that the search would start at F2 and go down the column until it found the first match. In this case, the return address would be $F$2 – which is what I am looking for.

You'll need to replace...

Set foundRng = Range("f2:f56").Find(CDate(Sheets("schedule").Range("a2").Value))

with

With Range("f2:f56")
    Set foundRng = .Find(CDate(Sheets("schedule").Range("a2").Value), After:=.Rows(.Rows.Count))
End With

 

[Marcelo Branco]

Hi Domenic,

Dealing with dates in VBA is tricky - I tried your code but it didn't work for me. I suppose because my Regional Setting for Date/Time is dd/mm/yyyy hh:mm:ss

If B2 contains just a date , not date/time, it worked perfectly even without CDate(...).

So, as a workaround i tried MATCH...

Is your Regional Setting = mm/dd/yyyy hh:mm:ss?

M.

 

[Domenic]

Hi Marcelo,

Yeah, dealing with dates is tricky. My regional setting is...

m/d/yyyy hh:mm:ss