Ironman
Well-known Member
- Joined
- Jan 31, 2004
- Messages
- 1,069
- Office Version
- 365
- Platform
- Windows
Hi
The below code in the Training Log sheet works almost perfectly
For some reason though, the line below is clearing the contents of the cell in Col F in the row above it, as shown in the mini table below and I can't figure out why this is happening.
There is some code in the Indoor Bike sheet that affects the same row (it's basically filling in the empty columns that the above code doesn't do) but I don't think this has any bearing on the above anomaly:
I'd be very grateful if this small anomaly can be resolved!
Thank you.
The below code in the Training Log sheet works almost perfectly
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextRow As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row + 1
Lr3 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row
If Target.Column = 2 And Target.Value = "OTHER" Then
Application.EnableEvents = False
Range("A" & Target.Row).Resize(, 6).Interior.Color = RGB(197, 217, 241)
Range("I" & Target.Row).Resize(, 2).Interior.Color = RGB(197, 217, 241)
Range("I" & Target.Row).Value = "Indoor bike session, 60 mins."
Sheets("Indoor Bike").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & lr).Value
Sheets("Indoor Bike").Range("B" & Lr2).Value = "1:00:00"
Sheets("Indoor Bike").Range("E" & Lr2).Value = "8"
Sheets("Indoor Bike").Range("J" & Lr2).Value = "Session "
Range("F" & Target.Row).Select 'move to this cell to input heart rate
MsgBox "Enter Average Heart Rate", vbInformation, "Indoor Bike Session Data"
End If
Sheets("Indoor Bike").Range("F" & Lr3).Value = Sheets("Training Log").Range("F" & lr).Value 'F = Heart Rate
' jump from F to H on the same row
If Target.Address(0, 0) = Range("F" & lr).Address(0, 0) Then 'F = Heart Rate
Range("H" & lr).Select 'H = Session rating
Sheets("Indoor Bike").Range("I" & Lr3).Value = Sheets("Training Log").Range("H" & lr).Value
Application.EnableEvents = True
End If
For some reason though, the line below is clearing the contents of the cell in Col F in the row above it, as shown in the mini table below and I can't figure out why this is happening.
VBA Code:
Sheets("Indoor Bike").Range("F" & Lr3).Value = Sheets("Training Log").Range("F" & lr).Value 'F = Heart Rate
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
358 | Wed, 29 Sep 2021 | 1:00:00 | 21.7 | 13.5 | 8 | 0% | 160 | J | Session 348. 3:00pm, Black Sabbath Live At Last (great, as ever). Started out v light and powerful, with slight angina (seems to be a trend after day's rest) until 40 mins when quads started burning and felt bit tired but good strong session overall, no idea watts output would be this high though! | |||
359 | Wed, 1 Sep 2021 | 1:00:00 | 8 | 111 | 69% | J | Session | |||||
Indoor Bike |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D358 | D358 | =IF(B358>0,C358*0.621,"") |
G358:G359 | G358 | =F358/(220-(DATEDIF($F$1,A358,"Y"))) |
There is some code in the Indoor Bike sheet that affects the same row (it's basically filling in the empty columns that the above code doesn't do) but I don't think this has any bearing on the above anomaly:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextRow As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
If Target.Address(0, 0) = Range("C" & lr).Address(0, 0) Then
Range("H" & lr).Select
MsgBox "Enter Average Watts", vbInformation, "Indoor Bike Session Data"
End If
If Target.Address(0, 0) = Range("H" & lr).Address(0, 0) Then
Range("J" & lr).Select
'Application.EnableEvents = True
End If
I'd be very grateful if this small anomaly can be resolved!
Thank you.