vba Range.Find problem with formatted cells

[nufan.us]

Hi everyone.

I have a form in which the user inputs some data, including the date.
When the user clicks on a button, I'm using the Range.Find function to find a cell in the range with the same date.

Now the function works fine, when the date is written in the cell as a long value, and the cell is not formatted as a cell that contains a date. (i.e. the cells value is 40598)
However, when I format the cell to show the date as "dd-mm-yy" instead of just the number (01-02-11) the find function doesn't work.

Can anyone help me?


Below is my code:

Dim worksheet
Dim findDate As Date
Dim convertDate As Long
Dim findRange As range

Set worksheet = Worksheets("expenses")

findDate = Me.date_input

convertDate = findDate


If (Trim(convertDate) <> "") Then
        With worksheet.range("6:68")
            Set findRange = .Find(What:=convertDate, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not findRange Is Nothing Then
                Application.Goto findRange, True
            Else
                MsgBox("Date not present")
            End If
        End With
End If

 

[Sandeep Warrier]

Hi


If the dates in your "expenses" worksheet are proper dates, the try the find function for findDate instead of convertDate, but use Lookin:=xlFormulas instead of Lookin:=xlValues.

I've found this to work many a time.

 

[nufan.us]

Thank you very much for your reply.

The problem however is, that the dates in my expenses form are not written as dates, but are calculated with a formula, which is why I can't use the Lookin:=xlFormulas.

I've also tried to use both the convertDate variable and the findDate as arguments for the find function, but it doesn't seem to work.
I've defined the findDate as a Date, and therefore it's formatted as "dd-mm-yyyy", but even if I change the formatting of the dates on the expenses-sheet, it still doesn't work.

The only way I've gotten it to work is by removing the formatting on the dates in the expenses-sheet, so they show the Long-number for the date instead, and then use the convertDate as argument for the find function.

But it's pretty hard to decipher the dates from the Long-number

I was wondering if it's possible in some way to retrieve from a cell what is actually displayed instead of the actual value or formula?

 

[nufan.us]

okay, I've found a way..

It's probably not the right way, but it seems to be working.
Before the find function, I use the Range.NumberFormat, and set it to "".
And after the find function I reinstate the "dd-mm-yy" formatting again by using the Range.NumberFormat.

It works perfectly, and is actually quite fast, even though it has to format and reformat a lot of cells.
But it's working!