Problem with code

[fiesta]

Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "J21"
Dim iRow As Long

iRow = Me.Range("L" & Rows.Count).End(xlUp).Row
If iRow < 19 Then
iRow = 19
End If
If iRow < 36 Then
If Me.Range(WS_RANGE).Value <> _
Me.Range("L" & Rows.Count).End(xlUp).Value Then
Cells(iRow + 1, "L").Value = Range(WS_RANGE).Value
End If
End If

End Sub

The above code works exactly as I want it to, ie : every time J21 is updated
the result is posted in L21 and if there is already data in that cell it moves down to the next cell L22 and so on down to L36.
The problem is that if the value of cell J21 is repeated consecutively it will
not post the data to the next available cell.
Any help please

 

[Von Pookie]

Right. What exactly are you wanting it to do?

You complained that the code did not add to column L if the value of J21 was the same as the last time it was ran--in which case J21=Last value in column L, so column L would not be updated (which is what your original code was written to do).

I removed that restriction so that even if the value of J21 was the same as the last value in column L, column L *would* update.

Then I'm afraid at this point I have no idea what exactly you are wanting the code to do...?

 

[fiesta]

Von
All I want to do is transfer the data in J21 about 16times each day starting
at L21 down one cell every time the sheet is updated. I thought it would be
straight forward but its proved otherwise.
Thanks for any help you can give me, but this is really the last time tonight.
Thanks again.

 

[Von Pookie]

I'm not sure what to tell you.

This is essentially the same code I posted before, just written a little differently. This works for me--if the last row found in column L is 36 or higher, column L does not update no matter if the value in J21 is repeated or not:

Private Sub Worksheet_Calculate()
Dim myRng As Range
Dim iRow As Long

Set myRng = Range("J21")
iRow = Range("L65536").End(xlUp).Row

If iRow < 19 Then iRow = 19

If iRow < 36 Then Cells(iRow + 1, "L").Value = myRng.Value

End Sub

 

[fiesta]

Von
Good and Bad news, still filling all the "L" column in the worksheet i've been
using all night.
I tried the code in a new workbook and download 16 results and it worked
perfectly.
So I will leave it like that for tonight and I thank you very much for all the
help you have given me its very much appreciated.