Steve,
You always post your solutions!! It's terrific! Thank you.
... and I'm glad you sorted it out.... that's some formula!
Lisa
Sent: Wed, Mar 13, 2013 3:14 am
Subject: [ExcelVBA] Re: Worksheet_Change Event to assign numbers via Row Number
Lisa,
I ended up taking your advice. That is, I used combination of a formula and a
Worksheet_Change Event to copy the formula down. I thought I would post this in
case it may help someone else someday.
The formula also includes the rest of the assigned number. For example the first
number for 2013 woud be "NCR2013-001".
Here's the formula I used:
=IF(C3="","","NCR"&TEXT(C3,"YYYY")&"-"&TEXT(IF($C3="","",IF(ISERROR(VALUE(RIGHT(B2,"1"))=1),1,IF(YEAR(C3)>YEAR(C2),1,RIGHT(B2,1)+1))),"000"))
Then for the Worksheet_Change Event I used the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C4:C10002")) Is Nothing Then
With Target(1, 0)
ActiveSheet.Unprotect
ActiveCell.Offset(-1, -1).Activate
ActiveCell.Copy
ActiveCell.Offset(1, 0).Activate
ActiveCell.PasteSpecial (xlPasteFormulas)
Application.CutCopyMode = False
ActiveCell.Offset(0, 2).Activate
ActiveSheet.Protect
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 (7) |
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