+ Reply to Thread
Results 1 to 9 of 9

Modifying sub macro from DATA to DATE

  1. #1
    Forum Contributor
    Join Date
    10-05-2014
    Location
    CALIFORNIA
    MS-Off Ver
    2010
    Posts
    1,807

    Modifying sub macro from DATA to DATE

    Hello,

    Can someone please modify this from data to date instead. The attachment is searching data for now. I want to switch to search dates.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    10-05-2014
    Location
    CALIFORNIA
    MS-Off Ver
    2010
    Posts
    1,807

    Re: Modifying sub macro from DATA to DATE

    Hello,

    When I type the date in the message gox i.e. 03/07/23, then it does not search the from A2:A17 because I believed it just searching with just the data. I want someone to modify it for dates search.

    Thanks

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,641

    Re: Modifying sub macro from DATA to DATE

    try using format the sFind exactly with format in column A
    PHP Code: 
    ActiveSheet.Range("$A$2:$A$17").AutoFilter Field:=1Criteria1:=Format(sFind"mm/dd/yy"
    Quang PT

  4. #4
    Forum Contributor
    Join Date
    10-05-2014
    Location
    CALIFORNIA
    MS-Off Ver
    2010
    Posts
    1,807

    Re: Modifying sub macro from DATA to DATE

    Bebo

    Thank you so much.

  5. #5
    Forum Contributor
    Join Date
    10-05-2014
    Location
    CALIFORNIA
    MS-Off Ver
    2010
    Posts
    1,807

    Re: Modifying sub macro from DATA to DATE

    Hi Bebo

    How to change this from A2 all the way down to the end of A column? The end of the column is 1048576. Do I change Range(“$A$2:$A$1048576”)? I tried this but when I search, There was some skip rows. Thank you


    ActiveSheet.Range("$A$2:$A$17")

  6. #6
    Forum Contributor
    Join Date
    10-02-2012
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    293

    Re: Modifying sub macro from DATA to DATE

    I wouldn't advise asking any macro or formula to check over 1 million different cells.

    Define a variable to identify the last used row.

    Enter the following at the start of the macro after "Dim sFind";

    Please Login or Register  to view this content.
    Then change ActiveSheet.Range("$A$2:$A$17") to ActiveSheet.Range("$A$2:$A$" & LastRow)

  7. #7
    Forum Contributor
    Join Date
    10-05-2014
    Location
    CALIFORNIA
    MS-Off Ver
    2010
    Posts
    1,807

    Re: Modifying sub macro from DATA to DATE

    Hi,
    I think i did right :o( ....

    Thank you so much

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    10-05-2014
    Location
    CALIFORNIA
    MS-Off Ver
    2010
    Posts
    1,807

    Re: Modifying sub macro from DATA to DATE

    Hi,

    I want to search just by Month and Year.

    My date setting in A:A example 03/09/23. But when I find or search all the month of March 2023 it does not do it.

    I tried to to =Format(sFind, "mm/yy") but did not work.

    Thank you

    Please Login or Register  to view this content.
    Last edited by RJ1969; 03-10-2023 at 02:58 PM.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,641

    Re: Modifying sub macro from DATA to DATE

    If you input like this in "mm/yy":
    03/22
    try
    PHP Code: 
    Sub test()
    Dim sFindstartDendD
    Dim LastRow 
    As Long
    LastRow 
    ActiveSheet.Cells(ActiveSheet.Rows.Count"A").End(xlUp).Row
    sFind 
    InputBox("Please enter search data""Search Box""")
    If 
    sFind "" Then Exit Sub
    startD 
    CDate("01/" sFind)
    endD CDate(WorksheetFunction.EoMonth(startD0))
    ActiveSheet.Range("$A$2:$A$" LastRow).AutoFilter Field:=1Criteria1:=">=" startDOperator:=xlAndCriteria2:="<=" endD
    Range
    ("A12").Select
    End Sub 

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help to join 2 sets of data by modifying MACRO into VBA
    By ec4excel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-29-2021, 03:35 AM
  2. Replace a blank cell in a column by a specific text using a macro
    By sai abhilash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2016, 03:16 PM
  3. Need help with adjusting/modifying macro to fit my data
    By thlee1122 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2015, 04:43 PM
  4. Need help modifying a macro to insert rows after date change.
    By Graham Pall in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-22-2015, 06:31 AM
  5. Macro: Need help modifying macro, pasting over a row of data
    By idontexcelinexcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-12-2014, 11:41 AM
  6. Modifying sort data macro
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2010, 05:43 PM
  7. Modifying VBA to create new macro for recording data
    By sighlent1 in forum Excel General
    Replies: 2
    Last Post: 12-30-2009, 08:26 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1