+ Reply to Thread
Results 1 to 3 of 3

Search Range and Write Date

Hybrid View

  1. #1
    Registered User
    Join Date
    01-03-2010
    Location
    North Carolina
    MS-Off Ver
    Excel 2007
    Posts
    12

    Search Range and Write Date

    Given a text string located in Cell R11, I need to search for that text string in a named range, get the address of the location of string, then set the time and date to a cell two columns to the right of the cell found in the search, and lastly select the cell that was written to so the user sees the value has been written. Below is what I have so far. However, no matter the entry in R11 the data/time is always being written to the second item in the range 2 columns over.

    Private Sub BtnEnter_Click()
        Dim rRng As Range
        Dim rStartCell As Range
        Dim strFindText As String
    
        strFindText = ThisWorkbook.Sheets("Participants").Range("R11").Text
    
        Set rRng = Range("Bib")
        Set rStartCell = rRng.Find(What:=strFindText)
    
        If Not rStartCell Is Nothing Then
           rStartCell.Offset(ColumnOffset:=2).Value = Date + Time
        Else
           MsgBox Title:="No Match", _
                      Prompt:="Search item was not found.", _
                      Buttons:=vbOKOnly
        End If
    End Sub
    Attached Files Attached Files
    Last edited by chadwellp; 01-04-2010 at 12:26 AM. Reason: added code tags

  2. #2
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Search Range and Write Date

    hello,

    seams to be your code is spot on though references are out a little insead of row 11 you want 7
    Private Sub BtnEnter_Click()
        Dim rRng As Range
        Dim rStartCell As Range
        Dim strFindText As String
    
        strFindText = ThisWorkbook.Sheets("Participants").Range("R7").Text
    
        Set rRng = Range("Bib")
        Set rStartCell = rRng.Find(What:=strFindText)
    
        If Not rStartCell Is Nothing Then
           rStartCell.Offset(ColumnOffset:=2).Value = Date + Time
           rStartCell.Select
        Else
           MsgBox Title:="No Match", _
                      Prompt:="Search item was not found.", _
                      Buttons:=vbOKOnly
        End If
    End Sub
    cheers.


  3. #3
    Registered User
    Join Date
    01-03-2010
    Location
    North Carolina
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Search Range and Write Date

    Thanks. It's amazing what you can overlook late at night.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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