Gary's code won't work if you are copying and pasting multiple rows. try
this instead

Private Sub Worksheet_Change(ByVal Target As Range)
for each cell in Target
if cell.column = 1 then
If IsEmpty(cell.Offset(0, 1).Value) Then
Application.EnableEvents = False
cell.Offset(0, 1).Formula = "=A" & cell.Row & "+1"
Application.EnableEvents = True
end if
End If
next cell
End Sub

"Gary''s Student" wrote:

There are two ways, EASY and HARD:

EASY:

Fill column B with:
=IF(A1="","",A1+1) and fill way down. As data get added in column A the
results appear in column B.

HARD:

Install this small worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
If Intersect(t, a) Is Nothing Then Exit Sub
If IsEmpty(t.Offset(0, 1).Value) Then
Application.EnableEvents = False
t.Offset(0, 1).Formula = "=A" & t.Row & "+1"
Application.EnableEvents = True
End If
End Sub
--
Gary''s Student - gsnu200773


"DAXU" wrote:

Hi,

For example, I have a excel sheet with two columns, A and B.
B's value is calculated based on A, e.g. =A1+1


My problem is that the formula is only applied to the cells selected.
I have to drag it to apply for other cells.
Is there a way that when new cell is added to column A, column B will
be automatically populated?

Many Thanks

Jerry