Unable to resolve small quirk in code

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,038
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,772
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
1,038
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
38,772
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
1,038
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
38,772
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,602
Messages
5,832,664
Members
430,153
Latest member
Javid_P

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