Auto Fill

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all
Is it possible to fill series or numbers without draging it or inserting manualy
e.
A
1 01/01/04 XY
2
If i put WXY in B2 i want date to enter by itself. How can i do it?
Thanks in advance
 
hi ali
one way would be to use the worksheet_change event. Put the following
code in your worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("B:B")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
Application.EnableEvents = False
With Me.Cells(Target.Row, "A")
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With

CleanUp:
Application.EnableEvents = True
End Sub

This will automatically enter a date in column A if you enter a value
in column B
Frank
 
Ali

To do it automatically you need formulas in column A that check Column B

=IF(B1<>"",TODAY(),"")

Note: this is volatile and will change daily, which I'm sure you don't want.

Another method is to use event code in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value <> "" Then
Excel.Range("A" & n).Value = Date ''Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Gord Dibben Excel MVP

Right-click on sheet tab and "View Code". Copy/paste the code in there.
 
Back
Top