Consulting

Results 1 to 7 of 7

Thread: can't run a simple recorded macro

  1. #1

    can't run a simple recorded macro

    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 ?

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    How does it crash?
    Is that date somewhere in E5:AB5 of the ActiveSheet?

  3. #3
    VBAX Expert
    Joined
    Apr 2005
    Posts
    810
    Location
    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

  4. #4
    Quote Originally Posted by mikerickson View Post
    How does it crash?
    Is that date somewhere in E5:AB5 of the ActiveSheet?

    image1.jpgimage2.jpgimage3.jpg

  5. #5
    Quote Originally Posted by jolivanes View Post
    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

  6. #6
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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

  7. #7
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •