Date Stamp

I've used this Macro and need to tweak it:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("a10"), .Cells) Is Nothing Then
            Application.EnableEvents = False
            With Me.Range("b10")
                .NumberFormat = "dd mmm yyyy"
                .Value = Now
            End With
            Application.EnableEvents = True
        End If
    End With
End Sub

This macro stamps B10 if something is entered in A10 but I also want this to 
continue to row 1000. Any help would be appreciated
0
Utf
3/24/2010 11:25:02 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
906 Views

Similar Articles

[PageSpeed] 6

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .cells.Count > 1 Then 
           Exit Sub
        end if

        If Intersect(me.Range("a10:1000"), .Cells) Is Nothing Then
            'do nothing
        else
            Application.EnableEvents = False
            With .offset(0,1)
                .NumberFormat = "dd mmm yyyy"
                .Value = Now
            End With
            Application.EnableEvents = True
        End If
    End With
End Sub


I made some completely arbitrary changes (a block if/then instead of the single
line if/then).  And I changed the "if .. is nothing" to something I find easier
to understand <vbg>.

If you want, you can change back.  It shouldn't be too difficult.


JeffK wrote:
> 
> I've used this Macro and need to tweak it:
> 
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>     With Target
>         If .Count > 1 Then Exit Sub
>         If Not Intersect(Range("a10"), .Cells) Is Nothing Then
>             Application.EnableEvents = False
>             With Me.Range("b10")
>                 .NumberFormat = "dd mmm yyyy"
>                 .Value = Now
>             End With
>             Application.EnableEvents = True
>         End If
>     End With
> End Sub
> 
> This macro stamps B10 if something is entered in A10 but I also want this to
> continue to row 1000. Any help would be appreciated

-- 

Dave Peterson
0
Dave
3/24/2010 11:38:50 PM
I pasted your code and the following error occured:

Method 'Range' of object'_Worksheet'failed

and highlighted

If Intersect(Me.Range("a10:1000"), .Cells) Is Nothing Then


"Dave Peterson" wrote:

> Try:
> 
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>     With Target
>         If .cells.Count > 1 Then 
>            Exit Sub
>         end if
> 
>         If Intersect(me.Range("a10:1000"), .Cells) Is Nothing Then
>             'do nothing
>         else
>             Application.EnableEvents = False
>             With .offset(0,1)
>                 .NumberFormat = "dd mmm yyyy"
>                 .Value = Now
>             End With
>             Application.EnableEvents = True
>         End If
>     End With
> End Sub
> 
> 
> I made some completely arbitrary changes (a block if/then instead of the single
> line if/then).  And I changed the "if .. is nothing" to something I find easier
> to understand <vbg>.
> 
> If you want, you can change back.  It shouldn't be too difficult.
> 
> 
> JeffK wrote:
> > 
> > I've used this Macro and need to tweak it:
> > 
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> >     With Target
> >         If .Count > 1 Then Exit Sub
> >         If Not Intersect(Range("a10"), .Cells) Is Nothing Then
> >             Application.EnableEvents = False
> >             With Me.Range("b10")
> >                 .NumberFormat = "dd mmm yyyy"
> >                 .Value = Now
> >             End With
> >             Application.EnableEvents = True
> >         End If
> >     End With
> > End Sub
> > 
> > This macro stamps B10 if something is entered in A10 but I also want this to
> > continue to row 1000. Any help would be appreciated
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/25/2010 7:37:01 PM
Never mind Dave I discovered the solution (a10:a1000)

Thanks for the help, you're always a great resource.

"Dave Peterson" wrote:

> Try:
> 
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>     With Target
>         If .cells.Count > 1 Then 
>            Exit Sub
>         end if
> 
>         If Intersect(me.Range("a10:1000"), .Cells) Is Nothing Then
>             'do nothing
>         else
>             Application.EnableEvents = False
>             With .offset(0,1)
>                 .NumberFormat = "dd mmm yyyy"
>                 .Value = Now
>             End With
>             Application.EnableEvents = True
>         End If
>     End With
> End Sub
> 
> 
> I made some completely arbitrary changes (a block if/then instead of the single
> line if/then).  And I changed the "if .. is nothing" to something I find easier
> to understand <vbg>.
> 
> If you want, you can change back.  It shouldn't be too difficult.
> 
> 
> JeffK wrote:
> > 
> > I've used this Macro and need to tweak it:
> > 
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> >     With Target
> >         If .Count > 1 Then Exit Sub
> >         If Not Intersect(Range("a10"), .Cells) Is Nothing Then
> >             Application.EnableEvents = False
> >             With Me.Range("b10")
> >                 .NumberFormat = "dd mmm yyyy"
> >                 .Value = Now
> >             End With
> >             Application.EnableEvents = True
> >         End If
> >     End With
> > End Sub
> > 
> > This macro stamps B10 if something is entered in A10 but I also want this to
> > continue to row 1000. Any help would be appreciated
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/25/2010 7:59:02 PM
Sorry about the typo.

Gald <vbg> you found it.

JeffK wrote:
> 
> Never mind Dave I discovered the solution (a10:a1000)
> 
> Thanks for the help, you're always a great resource.
> 
> "Dave Peterson" wrote:
> 
> > Try:
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> >     With Target
> >         If .cells.Count > 1 Then
> >            Exit Sub
> >         end if
> >
> >         If Intersect(me.Range("a10:1000"), .Cells) Is Nothing Then
> >             'do nothing
> >         else
> >             Application.EnableEvents = False
> >             With .offset(0,1)
> >                 .NumberFormat = "dd mmm yyyy"
> >                 .Value = Now
> >             End With
> >             Application.EnableEvents = True
> >         End If
> >     End With
> > End Sub
> >
> >
> > I made some completely arbitrary changes (a block if/then instead of the single
> > line if/then).  And I changed the "if .. is nothing" to something I find easier
> > to understand <vbg>.
> >
> > If you want, you can change back.  It shouldn't be too difficult.
> >
> >
> > JeffK wrote:
> > >
> > > I've used this Macro and need to tweak it:
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > >     With Target
> > >         If .Count > 1 Then Exit Sub
> > >         If Not Intersect(Range("a10"), .Cells) Is Nothing Then
> > >             Application.EnableEvents = False
> > >             With Me.Range("b10")
> > >                 .NumberFormat = "dd mmm yyyy"
> > >                 .Value = Now
> > >             End With
> > >             Application.EnableEvents = True
> > >         End If
> > >     End With
> > > End Sub
> > >
> > > This macro stamps B10 if something is entered in A10 but I also want this to
> > > continue to row 1000. Any help would be appreciated
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
3/25/2010 8:30:26 PM
Reply:

Similar Artilces:

How do I convert a number to a date format
I accedentally changed a column of dates to numbers, and now need to convert the serialized numbers back into dates... any ideas? can't you just reformat the #'s to dates -- duan ----------------------------------------------------------------------- duane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1162 View this thread: http://www.excelforum.com/showthread.php?threadid=26822 Right click on the cell, choose Format Cells, choose the Number tab, choose Date, find the way you want the date displayed, then OK. "CTausch" wrote: > I a...

Outlook 2000 saves calendar as webpage, but dates dont match days ???
when i save an outlook 2000 calendar as a web page all the 2005 dates (the 2004 dates seem ok) get shifted, so the 30th of may 2005 shows up as a sunday instead of a monday. any ideas? im wondering, is there a better place to post this question? seems like 'real' problem with the outlook program... similar questions in other forums (and no answers). could find nothing in ms 'knowledgebase'. no ideas on how to fix or work-a-round. thank you. "akm" wrote: > when i save an outlook 2000 calendar as a web page all the > 2005 dates (the 2004 dates seem ok) get ...

Date Selection on HR Training Reports
I am implementing Human Resources, Advanced Human Resources and Certifications, Licensing and Training On the standard HR training reports such as Employee Classes, date selection does not work. If I enter a date selection, nothing is printed on the report. Without date selection, everything prints. I found a KB article - 863263 that states this is an issue for V 8.0 but I can't find anything more frequent. This appears to still be an issue. Does anyone have an update or are you experiencing this as well. Thanks Becker, If you are a microsoft partner, You can log into Dynamics GP...

Date formats #3
I have a column in a spreadsheet containing dates.Some of the dates are in the date format while others are in a five digit format such as 38364 which is not recognisable as a date. How can I convert these numbers to provide me with a date. Mnay Thanks. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200605/1 Just highlight the complete column by clicking on the column identifier (the grey button at the top of the column), then Format | Cells | Number (tab) and choose Date with an appropriate format, then click OK. Your numbers like 38364 should now be...

Change date format in several worksheets
I have about 50 worksheets in a workbook and would like to change the date format in all. Is there a way to change all at once or must it be done worksheet by worksheet? Select first worksheet. Right-click on the tab and "select all sheets" to group them. Select data range in activesheet. CRTL + A(twice in XL 2003) will select all cells. Make your Format changes. What is done to one sheet will be done to all. DO NOT FORGET! to right-click on any tab and "ungroup" before making any other changes unless you want to change all sheets. Gord Dibben Excel MVP On Sat...

Date Part or Date Value
I have a form such that there are labels for each of the month and the control being a checkbox such that if a task is done in a particular month, then the checkbox for this month is ticked. I would like to add a textbox on this form and link it to a table such that if the checkbox for ie. Jan is checked then the textbox should show Jan-2008 if the year entered was 2008. This would then be stored in a table to use for calculations later on. Please help. On Sun, 3 Feb 2008 20:29:27 -0800 (PST), c8tz <ccholai@gmail.com> wrote: >I have a form such that there are labels for each of t...

SUMIFS with Dates
I have a conditional SUMIFS Detail Sheet Type Date Qty Credit 2/1/10 1 Sales 2/2/10 2 Credit 2/15/10 3 Summary Sheet Week # Start Date End Date Credits Sales 1 2/1/10 2/5/10 1 2 2 2/8/10 2/15/10 3 0 So, basicallay I am summing the Credit column if the Detail is Type Credit and the Date is >= the Start Date on the line and the End Date <= the Date on the line. Same for Sales. However, as simple as it seems, it just seems not lo l...

email sent/received dates
Hi, I'm using MSN Premium included with Verizon DSL and decided to take advantage of the 2g email storage and outlook connector for msn. Got it set up and working. I created a new folder to archive some email before I copy it to the archive pst on my pc so I can search it remotely. No problems. Today, I noticed all my dates changed on sent and received email that I moved. Worse, the header in the email (that outlook displays) shows this new date and doesn't reflect the original email date. I was moving mail from our company exchange server that was ready to be deleted to this fo...

Dynamics CRM 4.0
Hi, I've modified the format in the system settings screen to be "English (United Kingdom)", however all dates are still appearing in US format. Even when you expand the calendar control it displays incorrectly. Numeric values are correct - showing the pound sign and correct decimal point. I've restarted IIS, made sure all SQL logins are British English and the default regional settings of the server are set to British English. Even rebooted the server but this still hasn't corrected the problem. Can anybody tell me how to resolve this please Thanks I was looking ...

range color from date and database
newbie done some homework on excel i would like to have a range change color based on date and time and a name from a database--or any suggestion for example if it is thursday and the time is betweeen 1 and 3 pm i want the range to be say light red if out of tinme range i want it to be light green also the cell above the range as long as time constraints are met to have a name from a datasource any suggestions or ideas apprciated i need to have data from either a database or from say another sheet where user can enter a form the data from the datasource would fill range values a...

Date format question
I'm a new user of Outlook and am having a bit of difficulty with a small problem. I live in the Pacific NW, but need to have my calendar format as d/m/y verses the standard US m/d/y. I've got it right on my Pocket PC, but can't seem to find a setting to change it in Outlook. Any help in being able to change the calendar/date format would be greatly appreciated. Thank you. Michael The date format is being controlled by the Operating System; Control Panel-> Regional and Language Options... -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What ...

how to return a date
Hi all, I have these lines of code: D = Mid(Range(Cells(rijrij, kolkol), Cells(rijrij, kolkol)), N + 13, 100) If D <> "" Then If DateValue(D) < Date Then The content of Cells(rijrij, kolkol) was always like: Brink PN�M��03�04 24 februari 2010 D resulted in a date (continental European notation), in this case 24 februari 2010. The second and third lines of coded worked like intended. Now in some (not all) cases the content of Cells(rijrij, kolkol) is like: Berg PN�A��05�06 18 februari 2010 vonnis The differe...

calculating quantity on hand based on date-time stamp
Hello: I am always unclear on the proper syntax for pulling date-related data. I need to pull a field in my query, below, based on the field called "IV00118.CHANGEDATE_I". This is the date in which an end user changed the cost of an item. To give you some background, the IV00102 table in my query below is the Item Quantity Master table, while the IV00118 table is the Item Cost Change History table. The field that I want to pull based on that date field is the IV00102.QTYONHND field. This field is the inventory quantity on hand field. How would I format the syntax bel...

Formula to display Count of Days in given month, using list of Start and End Dates
I have a large number of rows that contain a start date and end date. I am attempting to generate a count of days within that date range that are in a particular month, for example, Jan of 2011. Any suggestions for a formula that could be used to display the amounts shown in Column C would be helpful. Layout is: A B C Start Date End Date Count of Days in Jan2011 1/10/11 4/7/11 21 2/6/11 3/1/11 0 12/20/10 2/15/11 31 Etc. Dave, I think the best way is to create a table f...

efficient method of finding closest date by specify input value
can it be specify the input value to the formate with YY-YY, that means let the use to key in 09-10 from the query. and the 09-10 means the date range (01/04/2009 to 31/03/2010) <staff> peter 01/08/2009 SAM 02/03/2009 IAN 03/01/2010 ==================> key in: 09-10 then, the output will shown below: output: peter 01/08/2009 IAN 03/01/2010 In query design view, enter something like this (as one line) into the Criteria row under your date field: >= DateSerial([From 1 April what year?],4,1) And < DateSerial([From 1 April what year?]+1,4,1) ...

Date format in Receipts
Hello: My boss wants to add the Due Date on each receipt that uses Account as tender. I created the variable on XML code using: <SET name="DueDate" type="vbDate"> Transaction.Date + 20 </SET> And print it with: <IF> <CONDITION> (Tender.Descriptor.TenderType = tenderAccount)</CONDITION> <THEN> <ROW> "Due Date|" DueDate </ROW> </THEN> </IF> This works, but the DueDate is printed as a number. How can I change its format to MM/DD/YYYY. I tried using all VB Date function, but no one worked. Please, hel...

Date formats in word
Using word 2007. If you type in a date, what appears to be an autocorrect option appears above. If you press enter the date is added to your date. If you press space it goes away. The date that is added is not the format we use in Austraila. How can that be changed. And how can this facility be disabled? When the date with the message Press enter to insert appears, just press the space bar instead. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally poste...

to show only last date from a table
I have a table for employee vacation dates. How can i get a text box in a form to show only the last vacation date? -- Mr. G. On Wed, 21 Nov 2007 14:37:01 -0800, Mr. G. <MrG@discussions.microsoft.com> wrote: >I have a table for employee vacation dates. How can i get a text box in a >form to show only the last vacation date? Set its control source to =DMax("[vacationdate]", "[tablename]", "[employeeID] = " & [EmployeeID]) using your own field and table names of course. John W. Vinson [MVP] Thanks -- Mr. G. "John W. V...

Some date &/or Times will not format
Good afternoon, I've imported some Date & Time data into excell, but when I try to format the colums as either date or time, some of the cells will not formatt. I've been told that sometimes importing data will result in hidden characters that will prevent formatting. Any way to correct this using a formula or other means? ex: 11/27/2009 16:23:00 PM . Best regards, -- Keith Nascarfan88 wrote: > Good afternoon, > > I've imported some Date & Time data into excell, but when I try to format > the colums as either date or time, some o...

Can't Convert Date to Text?
Having a hard time converting DATES to TEXT so I can sort the Dates by Month. Look at the attached spreadsheet. http://netmation.com/excelsrt.xls Col A = Date Col B = TEXT(DATE) Col C = Paste Special Value of Col B. Why won't Col B or C sort by Month, they all sort by year?? Using Excel 2007. col B use this =3DTEXT(A2,"mmm") Col C =3DTEXT(A2,"yyyy") now you can sort by month or year On Feb 4, 12:33=A0am, "Al Franz" <alb...@nospam.netmation.com> wrote: > Having a hard time converting DATES to TEXT so I can sort the Dates by > Month. =A0...

How to show current date and time
Hello, I want to show current date and current time in a single cell. I have tried by using today() and now() functions separately, i dont know how to use together in a sigle cell. I want like 28-Mar-06, 12.05 PM. Also I want to add text to it. Like Report for 28-Mar-06. Experts please help me Thanks Ravi -- vsr_kmb ------------------------------------------------------------------------ vsr_kmb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32834 View this thread: http://www.excelforum.com/showthread.php?threadid=527066 Try this: ="Report for "...

Save a date as a variable and run/convert formula against the vari
Good Day, What I'm needing to do is retrieve a date field from a closed workbook, save it as a variable and then calculate a week number based on it. which I'll use for another function. I've attached two types of code I'm struggling with in VB and hope someone can help... The first part retrieves a date from a close workbook and I need to change "MsgBox" to a varible to save the date retrieved. Sub GetVal() MsgBox TheValue("C:\GssReports", "gssreport MTTR.xlsx", "gssreport 1 ", "K6") End Sub Function...

Questions about formating Dates
I found documentation for this way to format a date: endtime.ToString("dddd, MMM d yyyy") But I can't find an explanation of this method: <%#Eval("CreateDate", "{0:d}")%> The first on produces Saturday, Apr 3 2010 Note no comma after the 3? Can I make it add a comma? The second one produces: Friday, April 02, 2010 Can I supress the leading zero? Best for me would be in both cases: Friday, April 2, 2010 Can I do that? Thanks " Cal Who" <CalWhoNOSPAM@roadrunner.com> wrote in message news:hp7u3a$71u$1@...

Date Math in Large Column
Hello! I have a huge spreadsheet (32000 rows). I have done a subtotal on part number. This does a sum on several other columns of values. There is a date column (B) with a date for each transaction for each part number. I am trying to come up with a way to determine the number of months over which the transactions occurred for each part number. The problem is that each subtotalled "group" is a different number of rows. I started to manually feed in this formula and then modify it for each group, but this could take days. =(max(b2:b20)-min(b2:b20))/30 This gives me ...

Need help in Dates Calculations.
Hello every Body. I have a DB in which i want that it will show me the report by taking a Parameter through keyboard and the Parameter value should be Date. It will take the date and filter all the records of the concern date. how would be it possible? Can any one help please...? the second thing is that when i give the date of birth in the DOB Field and Current Date in the Present Date Field it whould display the Difference in years between the two dates. But how.? urgent help needed please. Regards, Khaksar. See: http://allenbrowne.com/func-08.html The article explains some...