Integrate module into an existing worksheet_change event

Ironman

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

I have the following code, very kindly supplied by maabadi in a module
VBA Code:
Dim Lr1 As Long, Lr2 As Long
Lr1 = Sheets("Training Log").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row + 1
If UCase(Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 19))) = "INDOOR BIKE SESSION" Then
Sheets("Indoor Bike").Range("F" & Lr2 & ":G" & Lr2).Value = Sheets("Training Log").Range("F" & Lr1 & ":G" & Lr1).Value
Sheets("Indoor Bike").Range("I" & Lr2).Value = Sheets("Training Log").Range("H" & Lr1).Value
Sheets("Indoor Bike").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & Lr1).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 "
End If
End Sub
I also have the following code in a worksheet_change event, kindly supplied by NoSparks
VBA Code:
Dim NextRow As Long

lr = Range("A" & Rows.Count).End(xlUp).Row

If Target.Address(0, 0) = Range("A" & Rows.Count).End(xlUp).Address(0, 0) Then
Application.EnableEvents = False
    Range("C" & Target.Row).Select 'move to this cell to start inputting data

    MsgBox "Enter distance", vbInformation, "Indoor Bike Session Data"
End If

' jump from C to H on that same 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 just need the above sets of code to be combined as a worksheet_change event.

The code needs to start in Col C, not A as written above, because A is now copied as part of the first set of code. It then needs to jump from Col C to Col H and then Col J.

Many thanks!
 
Many thanks!

I've tried both:
VBA Code:
.Range("C" & .Rows.Count).End(xlUp).Select
Goes to the row above the newly added row so I tried
VBA Code:
.Range("C" & .Rows.Count).Enc(xlUp).Offset(1).Select
And it returned
VBA Code:
Run-time error 438
Object doesn't support this property or method

This is the code above your lines in Post #10:
VBA Code:
If Target.Column = 8 And Target.Row = Range("A" & Rows.Count).End(xlUp).Row Then
Range("H" & Target.Row).Validation.Delete 'added 31.10.2021 - clears validation input info, no longer needed
    Lr1 = Target.Row
    If UCase(Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 19))) = "INDOOR BIKE SESSION" Then
        Lr2 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets("Indoor Bike").Range("F" & Lr2 & ":G" & Lr2).Value = Sheets("Training Log").Range("F" & Lr1 & ":G" & Lr1).Value  'ave heart rate
        Sheets("Indoor Bike").Range("I" & Lr2).Value = Sheets("Training Log").Range("H" & Lr1).Value  'session rating
        Sheets("Indoor Bike").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & Lr1).Value  'date
        Sheets("Indoor Bike").Range("B" & Lr2).Value = "1:00:00"  'session length
        Sheets("Indoor Bike").Range("E" & Lr2).Value = "8"  'resistence level
Thanks again!
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Aha, no worries, I missed that too!

Thanks ever so much, it's just perfect now!

I presume this is no longer needed and can be deleted now?
VBA Code:
Private Sub Worksheet_Activate()

    lr = Range("A" & Rows.Count).End(xlUp).Row
    If Range("C" & lr) = "" Then
        Range("C" & lr).Select 'move to this cell to start inputting data
        MsgBox "Enter Distance", vbInformation, "Indoor Bike Session Data"
    
End If

End Sub
 
Upvote 0

Forum statistics

Threads
1,222,017
Messages
6,163,411
Members
451,835
Latest member
kristianb63

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