+ Reply to Thread
Results 1 to 11 of 11

VBA for FIND & SELECT DATES from cell with variable value

  1. #1
    Registered User
    Join Date
    12-17-2021
    Location
    Creighton, Southern Midlands of KZN, South Africa
    MS-Off Ver
    MS 365
    Posts
    11

    VBA for FIND & SELECT DATES from cell with variable value

    HELLO ALL,
    Quick question, maybe it's been covered already (I've searched but it does not cater for FIND & SELECT DATES
    I need to create a macro that inserts a cell value in the FIND & SELECT dialogue box based on a cell value THAT IS DATE FORMATTED and find a matching cell on another worksheet that is a formula value
    EG:-
    cell sheet2!B11 =+Sheet2!B3 = 5 DEC 21....but changes as users input new dates
    We need to find the value of B11...(5 DEC 21) in Sheet 1, which is a 2 years booking calendar for a small guest house
    and move to this cell.
    then move 2 cells down where we will then manually choose an available room and via a macro (CTR SHIFT R) input the reservation details

    So far I have this code and it works for numbers but I can't get the right syntax for the cell value...but I think I'm trying to over-think the answer. In LOOKIN, i've tried xlValues AND xlFormulas BUT ONLY xlValues WORKS

    Private Sub CommandButton1_Click()
    'Go to the reservation date as set Cell B8

    Dim FindString As String
    Dim Rng As Range
    FindString = Sheets("BOOKING REQUEST FORM").Range("B11").Value

    Sheets("CALENDAR 21-22").Activate
    If Trim(FindString) <> "" Then
    With Sheets("CALENDAR 21-22").Range("A:BY")
    Set Rng = .Find(What:=FindString, _
    After:=.Cells(1), _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False)
    If Not Rng Is Nothing Then
    Application.Goto Rng, True
    Else
    MsgBox "Nothing found"
    End If
    End With
    End If

    Sheets("CALENDAR 21-22").Activate
    MsgBox "GO TO THE RESERVATION DATE " & Worksheets("BOOKING REQUEST FORM").Range("D10").Value & vbCrLf & "SELECT CELL, PRESS CTRL SHIFT R", , "ENTER RESERVATION ON THE CHART"
    End Sub

    I hope someone can help...it would be a great christmas present
    Kind Regards and Seasons Greetings from South Africa
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: VBA for FIND & SELECT DATES from cell with variable value

    First of all welcome!
    Secondly if you put code inside code tags so it appears like below
    Please Login or Register  to view this content.
    it is easier to read its the # key on the toolbar

    I an not sure what you are really asking, but is the date always in the 5th row?
    =MATCH(B11,'CALENDAR 21-22'!B5:CB5,0)

    Why do you need to search?

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: VBA for FIND & SELECT DATES from cell with variable value

    This code works
    Please Login or Register  to view this content.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    12-17-2021
    Location
    Creighton, Southern Midlands of KZN, South Africa
    MS-Off Ver
    MS 365
    Posts
    11

    Re: VBA for FIND & SELECT DATES from cell with variable value

    Hi davsth
    The FIND & SELECT is to go directly to a date on a ws that is 110 col (3col per each month day) x 1500 rows (2 years of months) =A1:BY1500 and contains a RESERVATION CALENDAR to book off days/nights for a small guest house (7 rooms) It also contains merged cells

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: VBA for FIND & SELECT DATES from cell with variable value

    Have you seen post#3

  6. #6
    Registered User
    Join Date
    12-17-2021
    Location
    Creighton, Southern Midlands of KZN, South Africa
    MS-Off Ver
    MS 365
    Posts
    11

    Re: VBA for FIND & SELECT DATES from cell with variable value

    Hi Folks, I've re-loaded an amended file to hilite my problem
    Can find numbers BUT NOT date formats
    Thanks
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA for FIND & SELECT DATES from cell with variable value

    Phatt Chef,

    Just change to
    Please Login or Register  to view this content.
    Or if the matching date is always 1 cell then this is safer...
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    12-17-2021
    Location
    Creighton, Southern Midlands of KZN, South Africa
    MS-Off Ver
    MS 365
    Posts
    11

    Re: VBA for FIND & SELECT DATES from cell with variable value

    THANK YOU jindon
    Version 1 ...Worked PERFECTLY!!

  9. #9
    Registered User
    Join Date
    12-17-2021
    Location
    Creighton, Southern Midlands of KZN, South Africa
    MS-Off Ver
    MS 365
    Posts
    11

    Re: VBA for FIND & SELECT DATES from cell with variable value

    Thanks also to kvsrinivasamurthy and davsth for replies....all goes to help building my knowledge

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

    Re: VBA for FIND & SELECT DATES from cell with variable value

    For date, format can be in vary types
    PHP Code: 
    Private Sub DATE_SEARCH_Click()
    'Go to the reservation date as set Cell B8
    With Sheets("CALENDAR 21-22")
        For Each cell In .UsedRange
            If IsDate(cell) And cell.Value = Sheets("BOOKING REQUEST FORM").Range("B11").Value Then
                Application.Goto cell.Offset(1, 0), True
                MsgBox "GO TO THE RESERVATION DATE " & Worksheets("BOOKING REQUEST FORM").Range("D10").Value & vbCrLf & "SELECT CELL, PRESS CTRL SHIFT R", , "ENTER RESERVATION ON THE CHART"
                Exit Sub
            Else
            k = k + 1
            End If
        Next
    If k = .UsedRange.Count Then MsgBox "Nothing found"
    End With
    End Sub 
    For value, change "Isdate" to opposite "Not IsDate"
    PHP Code: 
    Private Sub DATENUMBER_SEARCH_Click()
    'Go to the reservation date as set Cell B8
    With Sheets("CALENDAR 21-22")
        For Each cell In .UsedRange
            If Not IsDate(cell) And cell.Value = Sheets("BOOKING REQUEST FORM").Range("B12").Value Then
                Application.Goto cell.Offset(3, 0), True
                MsgBox "GO TO THE RESERVATION DATE " & Worksheets("BOOKING REQUEST FORM").Range("D10").Value & vbCrLf & "SELECT CELL, PRESS CTRL SHIFT R", , "ENTER RESERVATION ON THE CHART"
                Exit Sub
                Else
                k = k + 1
            End If
        Next
    If k = .UsedRange.Count Then MsgBox "Nothing found"
    End With
    End Sub 
    Quang PT

  11. #11
    Registered User
    Join Date
    12-17-2021
    Location
    Creighton, Southern Midlands of KZN, South Africa
    MS-Off Ver
    MS 365
    Posts
    11

    Re: VBA for FIND & SELECT DATES from cell with variable value

    Thanks bebo021999
    Wonderful how many paths there are to the answer,,,,THANK YOU for a "simpler answer"???I think

+ 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] Macro to select a variable cell range and copy it down a variable amount of times
    By JPoFresh in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-18-2019, 09:17 PM
  2. Replies: 8
    Last Post: 07-20-2018, 11:52 AM
  3. Need help to find the min value across dates and another variable
    By maym in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-24-2017, 05:01 AM
  4. Using Find to select cells matching input variable
    By Nanga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-05-2010, 03:42 PM
  5. Replies: 2
    Last Post: 12-09-2008, 12:44 AM
  6. Assign current cell to a variable and re-select
    By Cumberland in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-29-2006, 10:47 PM
  7. Select cell with variable reference
    By haan0054 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2005, 04:05 PM

Tags for this Thread

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