I am trying to:
- Find a variable called LeftTD based on a string "Foot Strike" (this part of the code works, thanks to help from a previous question!)
- Find the next LeftTD in the list and call it LeftTDx
- Read/Message Box the LeftTD and the LeftTDx value minus 1
- Do this again until column H is empty
The input data looks like this: Excel Data
So I want the first message box to tell me, "step starts in row 9 and ends in row 28". The second message box I want to tell me, "step starts in row 29 and ends in row 33".
And the code, which runs as is because it only does steps 1 and 4 (see above) and I have everything else commented out:
Private Sub CommandButton1_Click()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim LeftStrike As Range, FrameLTD As Range, LeftStrike2 As Range 'COMMENT:, LeftTDx As Integer?
Dim lrL As Long
lrL = ws.Range("H" & ws.Rows.Count).End(xlUp).Row
'COMMENT: set LeftTD= to something?
Set LeftStrike = ws.Range("H2:H" & lrL) 'COMMENT: .Find(LeftTD)
For Each FrameLTD In LeftStrike
If InStr(FrameLTD, "Foot Strike") Then
LeftTD = FrameLTD.Offset(0, 1)
'COMMENT: LeftTDx = LeftTD.FindNext or is it too easy to ask VBA for : LeftTDx = LeftTD +1
'COMMENT:StepEnd = LeftTDx.value - 1
MsgBox "Step starts in row " & LeftTD & " and ends in row " 'COMMENT:& StepEnd
End If
Next FrameLTD
End Sub
So I guess my question is, how do I set the Find and Find Next code within my already established If Then statement that looks for the string?
Aucun commentaire:
Enregistrer un commentaire