insert number automatically vba

[rakesh seebaruth]

i refer to the above columns

A B C D E
DATE DETAILS REFERENCE MONTH YEAR
1
2
3
4
6
I have the following codes which adds the date,month, year in their respective cells

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A6")) Is Nothing Then
If Target.Cells.Count > 1 Then Exit Sub
Target.Value = Format(Date, "dd.mm.yyyy")
Target.Offset(, 3).Value = Month(Date)
Target.Offset(, 4).Value = Year(Date)
End If
End Sub



i want <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> to automatically add 001 in cell c1,002 in cell c2 ,003 in cell 3 and so on

thanks for your help

regards

rakesh

 

[ChrisBM]

Sub AddValues()

ActiveSheet.Range("C1").Value = "001"
ActiveSheet.Range("C2").Value = "002"
ActiveSheet.Range("C3").Value = "003"
ActiveSheet.Range("C4").Value = "004"

End Sub

Or if you would rather it loops through a bunch of cells:

Sub AddValuesLoop()

For i = 1 to 10

           ActiveSheet.Range("C" & i).Value = i

Next i

End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(Target, Range("A1:A6")) Is Nothing Then
 If Target.Cells.Count > 1 Then Exit Sub
 Dim ans As Long
 ans = Target.Row
 Target.Value = Format(Date, "dd.mm.yyyy")
 Target.Offset(, 3).Value = Month(Date)
 Target.Offset(, 4).Value = Year(Date)
 Target.Offset(, 2).NumberFormat = "000"
 Target.Offset(, 2).Value = ans
 End If
 End Sub