Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search and sign (Excel2003)

    I have to review and sign many worksheets in a number of workbook files each month. I want to write a macro to search through the workbook , find the "Chedked by" cell and put my name two cells to the right, then find the "date checked" cell and put a date I enter via textbox in the cell two cells to the right of that one. This will make it easier for me to get that step over with after I'v done my reviews. I tried starting the Find (F5) step but when I looked at the resulting macro, it wasnt' there. How do I get started? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search and sign (Excel2003)

    Do you want the macro to search a single worksheet, or all worksheets in a workbook, or all worksheets in all workbooks in a folder, or ...?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and sign (Excel2003)

    You mean the find step as in Edit, find. That should be recorded by a macro alright.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and sign (Excel2003)

    I would like it to search all worksheets in one workbook. Since I don't get to all the workbooks at once, I will need to enter a different date for each workbook

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search and sign (Excel2003)

    Try this:

    Sub SetChecked()
    Dim varInput
    Dim strName As String
    Dim dteDate As Date
    Dim wsh As Worksheet
    Dim rng As Range

    varInput = Application.InputBox(Prompt:="What's your name", _
    Default:="Torguemada", Type:=2)
    If varInput = False Then
    Exit Sub
    End If
    strName = CStr(varInput)
    varInput = Application.InputBox(Prompt:="Enter the date", _
    Default:=Date, Type:=2)
    If Not IsDate(varInput) Then
    Exit Sub
    End If
    dteDate = CDate(varInput)

    For Each wsh In ActiveWorkbook.Worksheets
    Set rng = wsh.Cells.Find(What:="Checked By", LookAt:=xlWhole)
    If Not rng Is Nothing Then
    rng.Offset(0, 2) = strName
    End If
    Set rng = wsh.Cells.Find(What:="Checked Date", LookAt:=xlWhole)
    If Not rng Is Nothing Then
    rng.Offset(0, 2) = dteDate
    End If
    Next wsh
    End Sub

    You can add more error checking, if you like.

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and sign (Excel2003)

    Works like a charm! Many thanks.

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and sign (Excel2003)

    This has been a big help. Is there a way to modify it to take a workbook, check it, and produce a workbook with the tab/cell address that is missing a signature or date? i want to hand this out to some users, who probably don't ahve a personal.xls to put it in, and say"run this to find out what's missing" Could it be distrubuted as an Add in, for instance? Thanks.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Search and sign (Excel2003)

    How about this modification to Hans's code?

    <pre>Option Explicit
    Sub SetChecked()
    Dim wMissing As Worksheet
    Dim lRow As Long
    Dim varInput
    Dim strName As String
    Dim dteDate As Date
    Dim wsh As Worksheet
    Dim rng As Range

    varInput = Application.InputBox(Prompt:="What's your name", _
    Default:="Torguemada", Type:=2)
    If varInput = False Then
    Exit Sub
    End If
    strName = CStr(varInput)
    varInput = Application.InputBox(Prompt:="Enter the date", _
    Default:=Date, Type:=2)
    If Not IsDate(varInput) Then
    Exit Sub
    End If
    dteDate = CDate(varInput)
    Set wMissing = Worksheets.Add
    With wMissing
    .Range("A1") = "Worksheet"
    .Range("B1") = "Cell"
    .Range("C1") = "Missing Info"
    lRow = 1
    For Each wsh In ActiveWorkbook.Worksheets
    If wsh.Name <> .Name Then
    Set rng = wsh.Cells.Find(What:="Checked By", LookAt:=xlWhole)
    If Not rng Is Nothing Then
    If rng.Offset(0, 2) = "" Then
    lRow = lRow + 1
    .Cells(lRow, 1) = wsh.Name
    .Cells(lRow, 2) = rng.Address(False, False)
    .Cells(lRow, 3) = "Name"
    End If
    rng.Offset(0, 2) = strName
    End If
    Set rng = wsh.Cells.Find(What:="Checked Date", LookAt:=xlWhole)
    If Not rng Is Nothing Then
    If rng.Offset(0, 2) = "" Then
    lRow = lRow + 1
    .Cells(lRow, 1) = wsh.Name
    .Cells(lRow, 2) = rng.Address(False, False)
    .Cells(lRow, 3) = "Date"
    End If
    rng.Offset(0, 2) = dteDate
    End If
    End If
    Next wsh
    End With
    End Sub</pre>



    To create an addin you could check out Jan Karel <!profile=Pieterse>Pieterse<!/profile> article on Creating Addins

    Steve

Posting Permissions

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