Excel NOW() problem

T

tvanharen

New Member
Joined
Apr 6, 2002
Messages
6
I am currently making a sheet that requires only one input from the user per row, with the rest of the cells in that row automatically filled in when the user enters the input in this specific cell in each row. Otherwise, the cells are blank. I've achieved this for most of the cells I want to do this for, except for cells that record the day, month, date, and time of the user's input into this one specific cell per row. I originally thought the NOW() function would be perfect, and in my original implementation, I thought it did work. The problem is this: the next day when I entered a new input in the input cell, the previous cells that used the NOW() function all updated with the current time, date, month, day, year, etc instead of keeping the original data from when the user input was originally made. Below is the formula I used to display the day of the week of the user's input. Any help with maybe getting the cells to freeze after input or converting the cells to their values and ignoring the formula after input would be much appreciated. It's not essential, but I would like to have this functionality in it. OK, here's the formula:

=IF(NOT(ISBLANK(E58)),CHOOSE(WEEKDAY(NOW()),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"),"")

Essentially the formula works like this: the user input cell in this case is cell E58. If this cell is blank, then nothing appears in it. If the cell has been issued an input value, the CHOOSE() function gets an index value from WEEKDAY(NOW()) and finds the corresponding day of the week and returns it. Again, the problem is getting the cell to retain its value instead of constantly updating when more input is done elsewhere in the sheet. Thank you to all who consider my problem and devote time to it.
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
Click to expand...

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
------------------------
 
Upvote 0
Jay, that works perfectly. I appreciate your help and the time you took to resolve my problem. Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Conditional HYPERLINKS to worksheet tab based on a cell value
Replies
6
Views
354
smozgur
smozgur
H
  • Question
'How to' question about a Dashboard
Replies
5
Views
198
etaf
etaf
A
  • Question
VBA to change cell colour based on content
Replies
3
Views
164
Snakehips
Snakehips
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
341
Agnarr
Agnarr
E
  • Question
Interest Due Log
Replies
6
Views
122
ExcelNew7
E

Forum statistics

Threads
1,214,830
Messages
6,121,835
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top