Ironman
Well-known Member
- Joined
- Jan 31, 2004
- Messages
- 1,069
- Office Version
- 365
- Platform
- Windows
Hi
An anomaly in my sheet code means I need to re-input a value in the last cell in a specific column for a cell value in another sheet to register correctly.
This is my code:
The below code, courtesy of DanteAmor, then selects the first blank cell in the Daily Tracking sheet in the current year column
What I'm looking for is for the above to be adapted slightly, so instead of selecting the first blank cell, it selects the cell above it, identifies the value in that cell and 're-enters' it.
Many thanks!
An anomaly in my sheet code means I need to re-input a value in the last cell in a specific column for a cell value in another sheet to register correctly.
This is my code:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("MilesToNextYearEndTotal") < 0 Then
MsgBox "Re-enter latest distance in Daily Tracking sheet", vbExclamation, "Y/E Total negative value anomaly correction"
Sheets("Daily Tracking").Select
End If
VBA Code:
Dim f As Range
Dim i As Long
Set f = Range("D1", Cells(1, Columns.Count).End(1)).Find(Year(Date), , xlValues)
If Not f Is Nothing Then
For i = 2 To Rows.Count
If Cells(i, f.Column).Value = "" Then
If i = 61 Then
If Day(DateSerial(Year(Date), 3, 1) - 1) = 29 Then
Cells(i, f.Column).Select
Exit Sub
End If
Else
Cells(i, f.Column).Select
Exit Sub
End If
End If
Next
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End SubWhat I'm looking for is for the above to be adapted slightly, so instead of selecting the first blank cell, it selects the cell above it, identifies the value in that cell and 're-enters' it.
Many thanks!
Last edited:
