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
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
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