Unable to resolve small quirk in code

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
852
Office Version
  1. 365
Platform
  1. Windows
Hi

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
ABCDEFGHIJ
358Wed, 29 Sep 20211:00:0021.713.580%160JSession 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!
359Wed, 1 Sep 20211:00:00811169%JSession
Indoor Bike
Cell Formulas
RangeFormula
D358D358=IF(B358>0,C358*0.621,"")
G358:G359G358=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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Lr3 is the last row number with data in it before you add the new data.
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
852
Office Version
  1. 365
Platform
  1. Windows
Thanks Rory, but the same logic applies to this row
VBA Code:
Sheets("Indoor Bike").Range("I" & Lr3).Value = Sheets("Training Log").Range("H" & lr).Value
and the cell in the same column above it isn't cleared.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If the cell is being cleared, then there is nothing in Sheets("Training Log").Range("F" & lr) when the code runs.
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
852
Office Version
  1. 365
Platform
  1. Windows
Hmm the rows above contain data as well.
125
118
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You're assigning a value from one sheet to another. The only way the destination cell can be cleared is if the cell on the source sheet is empty at the time the code runs.
 

Forum statistics

Threads
1,147,499
Messages
5,741,507
Members
423,663
Latest member
kaveh87rsh

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
Top