Problem with code

fiesta

Board Regular
Joined
Nov 4, 2005
Messages
84
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Can you provide a written explanation of what you would like this code to be doing? And you only want this to fire everytime cell J21 is changed? Please include all such details and permutations.
 
Upvote 0
I'm not quite 100% sure about the logic in the code but isn't that what it's supposed to do/what you are telling it to do?
 
Upvote 0
Hi Firefytr
I download Racing results every day to the same worksheet one race at a time to analyse it. The J21 cell is used to find the highest handicap in a race
using the MAX function and the result goes into cell L21 for race 1 and then L22 for race 2 and so on with a maximum of 16 races in a day.
 
Upvote 0
Hi Norie
Yes the code works fine until the result in cell J21 is the same as the last
update. Thats where i'm lost.
 
Upvote 0
What's the purpose of this if statement?
Code:
If Me.Range(WS_RANGE).Value <> Me.Range("L" & Rows.Count).End(xlUp).Value Then
 
Upvote 0
What Norie is trying to say is that there is nothing wrong with the code--it is doing exactly what it should be doing.

Code:
f Me.Range(WS_RANGE).Value <> _
Me.Range("L" & Rows.Count).End(xlUp).Value Then
Cells(iRow + 1, "L").Value = Range(WS_RANGE).Value

What this line says is "If the value of J21 matches the value of the last value entered into Column L, *do nothing*. If it does *not* match, the value of J21 will be entered into column L.

The code is not doing anything other than what it is coded to.
 
Upvote 0
Well, assuming you are now wanting it to update column L no matter what, I believe this should work:

Code:
Private Sub Worksheet_Calculate()
Dim iRow As Long

iRow = Range("L65536").End(xlUp).Row
If iRow < 19 Then iRow = 19

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

End Sub
 
Upvote 0
Hi Von
Sorry that is filling all the cells in "L" every time I update the worksheet.
I've really had it now have been on this for hours it seems.
 
Upvote 0

Forum statistics

Threads
1,214,070
Messages
6,117,524
Members
448,766
Latest member
00Cameron

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top