Excel NOW() problem - Page 2
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Excel NOW() problem

  1. #11
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    OK Jay, here's what's happening. First of all, here is the event I'm using and the formulas in the pertinent cells. Hopefully I can explain what's going on so I don't confuse you.

    'Here's what I added to the module
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 9 And Len(Target) > 0 Then
    Target.Offset(0, -7) = Format(Date, "mmmm d, yyyy")
    Target.Offset(0, -6) = Format(Date, "dddd")
    Target.Offset(0, -5) = Format(Date, "M")
    Target.Offset(0, -4) = Format(Text)
    Target.Offset(0, -3) = Format(Text)
    Target.Offset(0, -2) = Format(Time, "hh:mm")
    End If
    If Len(Target) = 0 Then
    Target.Offset(0, -7) = ""
    Target.Offset(0, -6) = ""
    Target.Offset(0, -5) = ""
    Target.Offset(0, -4) = ""
    Target.Offset(0, -3) = ""
    Target.Offset(0, -2) = ""
    End If
    End Sub

    And here's a sample row of my sheet with the formulas that are in each cell (I hope you can make some sense of it):



    A58 contains: =IF(NOT(ISBLANK(I57)),ROW(A58)-14,"") //returns game number

    B58 contains: =IF(NOT(ISBLANK(I58)),TODAY(),"") //returns current date

    C58 contains: =IF(NOT(ISBLANK(I58)),CHOOSE(WEEKDAY(NOW()),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"),"") //returns current day of the week

    D58 contains: =IF(NOT(ISBLANK(I58)),CHOOSE(MONTH(NOW()),"January","February","March","April","May","June","July","August","September","October","November","December"),"") //returns current month

    E58 contains: =IF(NOT(ISBLANK(I58)),DAY(NOW()),"") //returns current day of the month

    F58 contains: =IF(NOT(ISBLANK(I58)),YEAR(NOW()),"") //returns current year

    G58 contains: =IF(NOT(ISBLANK(I58)),TIME(HOUR(NOW()),MINUTE(NOW()),0),"") //returns current time

    H58 contains: =IF(NOT(ISBLANK(I58)),I58-$D$1,"") //returns value under par

    I58 contains: no formula. (this is the user input cell that takes an integer score around which all other row formulas are based)

    Cells A58 and H58 work fine because the NOW() or TODAY() functions aren't being used.

    OK, now, when I include the event above I get the message: "Run-time error '1004': Application-defined or object-defined error" and points to Target.Offset(0, -7) = "". When I comment out the second If block compeletely, I end up with the following message: Run-time error '13': Type mismatch" except that it changes cells B58, C58, and G58 correctly, leaves cells E58 and F58 empty, and changes cell D58 to a number corresponding with the month I want instead of just the month in text. Let me know if you need to clarify anything, because I'm sure I'll have to. Thanks again for the help.

  2. #12
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-08 11:04, tvanharen wrote:
    OK Jay, here's what's happening. First of all, here is the event I'm using and the formulas in the pertinent cells. Hopefully I can explain what's going on so I don't confuse you.

    'Here's what I added to the module
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 9 And Len(Target) > 0 Then
    Target.Offset(0, -7) = Format(Date, "mmmm d, yyyy")
    Target.Offset(0, -6) = Format(Date, "dddd")
    Target.Offset(0, -5) = Format(Date, "M")
    Target.Offset(0, -4) = Format(Text)
    Target.Offset(0, -3) = Format(Text)
    Target.Offset(0, -2) = Format(Time, "hh:mm")
    End If
    If Len(Target) = 0 Then
    Target.Offset(0, -7) = ""
    Target.Offset(0, -6) = ""
    Target.Offset(0, -5) = ""
    Target.Offset(0, -4) = ""
    Target.Offset(0, -3) = ""
    Target.Offset(0, -2) = ""
    End If
    End Sub

    And here's a sample row of my sheet with the formulas that are in each cell (I hope you can make some sense of it):



    A58 contains: =IF(NOT(ISBLANK(I57)),ROW(A58)-14,"") //returns game number

    B58 contains: =IF(NOT(ISBLANK(I58)),TODAY(),"") //returns current date

    C58 contains: =IF(NOT(ISBLANK(I58)),CHOOSE(WEEKDAY(NOW()),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"),"") //returns current day of the week

    D58 contains: =IF(NOT(ISBLANK(I58)),CHOOSE(MONTH(NOW()),"January","February","March","April","May","June","July","August","September","October","November","December"),"") //returns current month

    E58 contains: =IF(NOT(ISBLANK(I58)),DAY(NOW()),"") //returns current day of the month

    F58 contains: =IF(NOT(ISBLANK(I58)),YEAR(NOW()),"") //returns current year

    G58 contains: =IF(NOT(ISBLANK(I58)),TIME(HOUR(NOW()),MINUTE(NOW()),0),"") //returns current time

    H58 contains: =IF(NOT(ISBLANK(I58)),I58-$D$1,"") //returns value under par

    I58 contains: no formula. (this is the user input cell that takes an integer score around which all other row formulas are based)

    Cells A58 and H58 work fine because the NOW() or TODAY() functions aren't being used.

    OK, now, when I include the event above I get the message: "Run-time error '1004': Application-defined or object-defined error" and points to Target.Offset(0, -7) = "". When I comment out the second If block compeletely, I end up with the following message: Run-time error '13': Type mismatch" except that it changes cells B58, C58, and G58 correctly, leaves cells E58 and F58 empty, and changes cell D58 to a number corresponding with the month I want instead of just the month in text. Let me know if you need to clarify anything, because I'm sure I'll have to. Thanks again for the help.
    Hi,

    You do not need the formulas in the cells if you are using this event macro. The event triggers the cells to populate with the data as you want. It even loads the formulas at "event" time.

    So, clear the A:H cells in your range, and load the following (I changed the references because I don't like the .offset method. Keep it if you'd like).

    --------------------
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column = 9 And Len(Target) > 0 Then
    Cells(Target.Row, "A") = "=IF(NOT(ISBLANK(" & Cells(Target.Row - 1, 1).Address(False, False) & ")),ROW()-14,"""")"
    Cells(Target.Row, "B") = Date
    Cells(Target.Row, "B").NumberFormat = "mmmm d, yyyy"
    Cells(Target.Row, "C") = Format(Date, "dddd")
    Cells(Target.Row, "D") = Format(Date, "mmmm")
    Cells(Target.Row, "E") = Format(Date, "d")
    Cells(Target.Row, "F") = Format(Date, "yyyy")
    Cells(Target.Row, "G") = Format(Time, "hh:mm")
    Cells(Target.Row, "H") = "=RC[+1]-R1C4"
    End If
    If Target.Column = 9 And Len(Target) = 0 Then
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "H")).Clear
    End If
    Application.EnableEvents = True

    End Sub
    ------------------------

  3. #13
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Jay, that works perfectly. I appreciate your help and the time you took to resolve my problem. Thanks!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com