Hey Steve,
How are the dates entered?
Do people just enter the month and day and let excel add a year?
Do people enter the year as well?
What about entering a formula into the cell adding one to the cell above and if the year changes enter a 1 instead?
HTH
Lisa
Sent: Tue, Mar 12, 2013 3:40 pm
Subject: [ExcelVBA] Worksheet_Change Event to assign numbers via Row Number
Hi,
I have the code below that I used to automatically assign the row number in a
cell (in column C) when a date is entered in the adjacent cell to the right
(column D). My first row is row 3, which is why I have "-2" in the formula for
the assigned number so that it starts with "001".
The problem I have now is that this code was good for the current year only and
then I was asked would it reset to "001" when the year changes to "2014". So
goes the idea of using the row numbers. I'm looking for help on how to make this
based on the assigned number directy above and the year number in my adjacent
column to the right (my column D) so that it will reset at "001". Any assistance
would be greatly appreciated.
Also, I set the range to 500 rows. How can I leave that out so that I can use as
many rows as I need without having to change my code again if I run past row
502?
Thanks,
Steve
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Target.Cells.Count > 1 Then Exit Sub
RowNumber = ActiveCell.Row
If Not Intersect(Target, Range("D3:D502")) Is Nothing Then
With Target(1, 0)
.Value = RowNumber - 2
End With
End If
End Sub
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar