Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to check cells in range (Excel 97 SR2)

    Hi all,
    I have been asked by a co-worker for a macro to check a single column range starting in C2 and going down. There will be formulas that result in dates. These are to be accepted and the next cell down is to be checked. When the macro encounters a formula that does NOT result in a date, the macro should clear the contents of that cell, and assign a range name (to be determined, I assume it will be fixed) to the range C2 through the last cell that had a formula that resulted in a date. I am including his dummy workbook (the formulas are not the ones that will be used - I will help him develop the formula to use).
    As usual, this request came at the end of my day (30 mins to departure) so he does not expect me to let him know today. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Thanks for your help.
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    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: Macro to check cells in range (Excel 97 SR2)

    Does this do what you want? Change the named range as desired

    Steve

    <pre>Option Explicit
    Sub CheckDate()
    Dim x As Long
    Dim rng As Range
    Dim rcell As Range
    Set rng = Range("c2")
    If Not IsDate(rng) Then
    MsgBox "C2 must be a date"
    Exit Sub
    End If
    x = 1
    Do
    Set rcell = rng.Cells(1).Offset(x, 0)
    If IsDate(rcell) Then
    Set rng = Union(rng, rcell)
    x = x + 1
    Else
    rcell.ClearContents
    End If
    Loop While IsDate(rcell)

    rng.Name = "NamedRange"

    Set rcell = Nothing
    Set rng = Nothing
    End Sub</pre>


  3. #3
    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: Macro to check cells in range (Excel 97 SR2)

    Not sure what you are after but you could name the range automatically with a dynamic range name. This won't clear the first null nor checks for dates but if it is date or a null it should work:

    Insert - name -define
    Name: CountDates
    refers to:
    =COUNT(Sheet1!$C:$C)
    [Add]
    Name:NamedRange
    Refers to:
    =OFFSET(Sheet1!$C$2,0,0,CountDates,1)
    [ok]

    Now the range named "NamedRange" will change as the formulas change.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to check cells in range (Excel 97 SR2)

    Steve,

    Thanks so much for the quick reply. I tried the first macro and it seemed to work great.
    Have not done anything with the second macro portion (as of yet).
    I passed it on to the user and have not heard back yet.
    I Really Love this site and all the help that is so freely given.
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to check cells in range (Excel 97 SR2)

    Steve,

    I looked at the second message you sent. It is not, of course a macro. I have actually used this method (I learned on this site) and it works super for making Dynamic Range Names.
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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