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
--- In ExcelVBA@yahoogroups.com, 1z@... wrote:
>
> Hmmmmm...
>
>
> You might even be able to set up a non vba solution....
>
>
> =IF(D3="","",IF(YEAR(D3)>YEAR(D2),1,C2+1))
>
>
> In C3 and then copy down a few thousand rows.
>
>
> HTH
> Lisa
>
>
>
>
>
> [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 (6) |
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