PDA

View Full Version : can't run a simple recorded macro



Ramo964516
02-27-2016, 04:05 PM
Hi everybody,

I'm working on a macro, and needed to know how to write a code with a find formula. I decided to record a simple macro in excel that will do exactly what i want to do. after recording the marco, i tried to run it but it crashes. the code is this:



Sub Macro3()
'
' Macro3 Macro
'

'
Range("E5:AB5").Select
Selection.Find(What:="29/01/2016", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("X5").Select

End Sub
Can someone help me ?

mikerickson
02-27-2016, 05:27 PM
How does it crash?
Is that date somewhere in E5:AB5 of the ActiveSheet?

jolivanes
02-27-2016, 10:27 PM
What about this?

Sub Macro3()
'
' Macro3 Macro
'

'
Range("E5").Select
Range("E5:W5").Find(What:="29/01/2016", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub

Ramo964516
02-28-2016, 02:44 AM
How does it crash?
Is that date somewhere in E5:AB5 of the ActiveSheet?


154881548915490

Ramo964516
02-28-2016, 02:46 AM
What about this?

Sub Macro3()
'
' Macro3 Macro
'

'
Range("E5").Select
Range("E5:W5").Find(What:="29/01/2016", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub


it does'nt work neither Jolivanes

mikerickson
02-28-2016, 07:24 AM
The code is looking for "29/01/2016".
The cell contains "29/02/2016"

It is finding nothing and, therefore, erroring when it tries to Activate Nothing.

Try

Dim myCell as Range

Set myCell = Range("E5:W5").Find(What:="29/01/2016", After:=Range("E5"), LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If myCell is Nothing then
MsgBox "nothing found"
Else
myCell.Activate
End If

Ramo964516
03-01-2016, 01:48 AM
Hi Mikerickson, thanks fo your answer. It turns out that Excel didn't recognize the date type of my data. So i used DateSearial in my Find function, then it works very well.