PDA

View Full Version : VBA - finding date in another worksheet



aymericdv
03-20-2016, 03:45 PM
Hi

I would love some help on the below.

FindDate = 29/02/2016 (this is defined by C3 in the "INPUTS" tab).

When I run the macro below, I get a message box and "nope", whereas I'd like the macro to select cell AI9 of the "Corporate" tab which also has the date 29/02/2016 in it. I've probably not explained everything you need to know but if not, then please tell me why the macro isn't doing what I want it to.

Many thanks!


Sub Macro2()
Dim MI As Workbook
Dim DataRange1 As Range
Dim FindDate As Date
Dim Loc As Range
Set MI = ThisWorkbook
Set DataRange1 = MI.Worksheets("Corporate").Range("9:9")
FindDate = MI.Sheets("INPUTS").Range("C3")

If Trim(FindDate) <> "" Then
With DataRange1
Set Loc = .Find(what:=FindDate)
If Not Loc Is Nothing Then
Application.Goto Loc, True
Else
MsgBox "nope"
End If
End With
End If

End Sub

SamT
03-20-2016, 07:29 PM
Try
.Find(lookIn:=xlValues, ...)

Also try
IF IsDate(Trim(FindDate)) Then

Finally, try adding this to your code

'
'
Dim X, Y
X = FindDate
Y = Worksheets("Corporate").Range("AI9")
'
'
'

Step thru the code by pressing F8 and hover the mouse over X and Y after the Moving Yellow Highlight passes them