Thanks:  0
Likes:  0

1. 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. 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. Jay, that works perfectly. I appreciate your help and the time you took to resolve my problem. Thanks!

## User Tag List

#### Posting Permissions

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