Selasa, 12 Maret 2013

[ExcelVBA] Re: Worksheet_Change Event to assign numbers via Row Number

 

Hi Lisa,

The dates are entered as mm/dd/yy manually or by using the Calendar I set up using the link below:

http://www.fontstuff.com/vba/vbatut07.htm

I hadn't thought of using a formula. I'll have to think a bit on how I might use it.

Thanks,

Steve

--- In ExcelVBA@yahoogroups.com, 1z@... wrote:
>
> 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 (4)
Recent Activity:
----------------------------------
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