jeudi 14 mai 2020

How do I use Find and FindNext within another If Then statement?

I am trying to:

  1. Find a variable called LeftTD based on a string "Foot Strike" (this part of the code works, thanks to help from a previous question!)
  2. Find the next LeftTD in the list and call it LeftTDx
  3. Read/Message Box the LeftTD and the LeftTDx value minus 1
  4. 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