Macro to find specified date in a range

[LucasLondon]

Hello

I have the following code that takes a date in a cell from one sheet "Calculation". and finds it another sheet "Latest Data" and then performs some actions. However, it says it cannot find the value/date even though it exists in both sheets.

So for example, A3296 in the "Calculation" sheet contains the value "24/06/2010". Th macro reads this cell and sets it as the find item (x). This works fine. But when it attempts to find this date in the "Latest Data" sheet, it cannot find it even though "24/06/2010" exists the cell A461.

Any ideas why this is not working. I am attaching an example.

Thanks,

Lucas

 
Sub Updateindex()
Application.ScreenUpdating = False
Dim Rng As Range
Dim startrow, endrow As Integer
Dim findstring, x As String
Sheets("Calculation").Range("a2500").End(xlDown).Select
x = ActiveCell.Value
findstring = x
If Trim(findstring) <> "" Then
    Sheets("Latest Data").Select
    With Sheets("Latest Data").Range("A:A")
        Set Rng = .Find(What:=findstring, After:=Sheets("Latest Data").Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, SearchFormat:=False, MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
            MsgBox "Nothing found"
        End If
    End With
    End If
startrow = Selection.Offset(1, 0).Row
endrow = Range("a2000").End(xlUp).Row
Range("A" & startrow & ":" & "T" & endrow).Copy
Sheets("calculation").Select
Range("a4000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues

[Bob Phillips]

If it is a date, set LookIn to xlFormulas.

[LucasLondon]

Hi,

Thanks for the suggestion.

I've changed LookIn to xlFormulas but is still not working, i.e it can't find the values.

Could there be any other reason why this does not work?

Thanks,

Lucas

[Bob Phillips]

This seems to work

Sub Updateindex()
Application.ScreenUpdating = False
Dim Rng As Range
Dim startrow, endrow As Integer
Dim findstring, x As String
Sheets("Calculation").Range("a2500").End(xlDown).Select
x = ActiveCell.Value
findstring = x
If Trim(findstring) <> "" Then
    Sheets("Latest Data").Select
    With Sheets("Latest Data").Range("A:A")
        Set Rng = .Find(What:=CDate(findstring), _
        After:=Sheets("Latest Data").Range("A1"), _
        LookIn:=xlFormulas, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, _
        SearchFormat:=False, _
        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
            MsgBox "Nothing found"
        End If
    End With
    End If
startrow = Selection.Offset(1, 0).Row
endrow = Range("a2000").End(xlUp).Row
Range("A" & startrow & ":" & "T" & endrow).Copy
Sheets("calculation").Select
Range("a4000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.ScreenUpdating = True
End Sub

[LucasLondon]

Yep,

This also works for me.

Thanks,

Lucas