Private Sub Worksheet_Calculate( )
Range("L" & 21 + Application.WorksheetFunction.Count([L21:L35])) = [J21].Value
End Sub

The above will not run automatically, but will run if I enter the Module and
press
the Run Sub Button.
Help please

Probably because you have stored it in the wrong place. It should go in the
worksheet code module, not a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave" wrote in message
...
Private Sub Worksheet_Calculate( )
Range("L" & 21 + Application.WorksheetFunction.Count([L21:L35])) =
[J21].Value
End Sub

The above will not run automatically, but will run if I enter the Module
and
press
the Run Sub Button.
Help please

Hi Bob
Thanks, it works auto now but its populating all the cells L21:L35 which I
dont
want. What it should do, if L21 is full then go to L22 and so on down to L35
every time J21 changes.
I'm stuck now not very good with VBA.


"Bob Phillips" wrote:

Probably because you have stored it in the wrong place. It should go in the
worksheet code module, not a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave" wrote in message
...
Private Sub Worksheet_Calculate( )
Range("L" & 21 + Application.WorksheetFunction.Count([L21:L35])) =
[J21].Value
End Sub

The above will not run automatically, but will run if I enter the Module
and
press
the Run Sub Button.
Help please

Dave,

Different event.

Remove your code and then add this

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "J21"
Dim iRow As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
iRow = Me.Range("L" & Rows.Count).End(xlUp).Row + 1
If iRow < 21 Then
iRow = 21
End If
If iRow < 36 Then
Cells(iRow, "L").Value = Target.Value
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave" wrote in message
...
Hi Bob
Thanks, it works auto now but its populating all the cells L21:L35 which I
dont
want. What it should do, if L21 is full then go to L22 and so on down to
L35
every time J21 changes.
I'm stuck now not very good with VBA.


"Bob Phillips" wrote:

Probably because you have stored it in the wrong place. It should go in
the
worksheet code module, not a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave" wrote in message
...
Private Sub Worksheet_Calculate( )
Range("L" & 21 + Application.WorksheetFunction.Count([L21:L35])) =
[J21].Value
End Sub

The above will not run automatically, but will run if I enter the
Module
and
press
the Run Sub Button.
Help please