VBA to find row address in column

[Westbury]

I 'm trying to include a step in a macro to find the row in col C where a known date occurs. There's only one location on a particular worksheet.

I've researched this issue here and else where but haven't succeeded in solving the problem!

I'm using a date format d/mm/yyyy for data entry so today is 28/07/2024 but the display is 28 Jul 24.

Dim latestreaddate As Date
latestreaddate = Format(latestreaddate, "Short Date") ' this line correctly identifies 20/06/2024 from my ws
Set FoundCell = Range("C:C").Find(What:=DateValue(latestreaddate), LookIn:=xlValues) 'but this line says FoundCell =Nothing

As ever, would be grateful for some help here.
thanks
Geoff

 

[vmjan02]

@Westbury
To solve your problem, you need to ensure that the Find method correctly interprets the date value and matches it with the format in the worksheet

try this

Dim latestreaddate As Date
Dim FoundCell As Range

' Assign a date value to latestreaddate
latestreaddate = DateValue("20/06/2024") ' or however you assign your date

' Ensure the format matches the date format in your worksheet
Dim dateToFind As String
dateToFind = Format(latestreaddate, "dd mmm yy") ' This should match the display format in the worksheet

' Find the cell in column C that matches the formatted date string
Set FoundCell = Columns("C:C").Find(What:=dateToFind, LookIn:=xlValues, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then
    MsgBox "Found the date at row: " & FoundCell.Row
Else
    MsgBox "Date not found"
End If

 

[dmt32]

Hi,
Although Range.Find method can sometimes prove a little tricky when searching for dates, providing you are working with real dates in your range then the applied range.numberformat should not affect the find function locating the required date

Try updating your code as follows & see if resolves your issue.

Dim latestreaddate  As Date
Dim FoundCell       As Range

latestreaddate = Range("H2").Value
Set FoundCell = Range("C:C").Find(What:=latestreaddate, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not FoundCell Is Nothing Then MsgBox FoundCell.Address

In this example I have assumed Range("H2") contains a real date value.

Dave

 

[Fuji]

Use of Match method.

    Dim myDate As Date, myRow
    myDate = DateSerial(2024, 7, 28)
    myRow = Application.Match(CLng(myDate), Columns("c"), 0)
    If IsNumeric(myRow) Then
        MsgBox myRow
    Else
        MsgBox "No matched date"
    End If

 

[Alex Blakenburg]

the applied range.numberformat should not affect the find function locating the required date

This is true if you are using xlFormulas as in your post but not if you are using xlValues as in posts #1 and #2.
If columns C contains constants then your xlFormulas is fine but if it contains formulas then you are going to need xlValues which is format dependent.
If you only need to find 1 or the first occurence I am also quite partial to Match which is much more forgiving.

 

[Westbury]

vmjan02, dmt32, Fuji, Alex,

Thank you all for your replies. I've updated my code, it works fine and I've learnt something new!

Geoff.