CODEWITHSUNDEEP
excelvbavariablesrange
Alberto Brown
Alberto Brown

Reputation: 355

excel vba find start and end row in column for multiple variables

In excel, I have a helper column (col O) that has a formula in each cell, the formula lets me know when the data changes from 1 to 2 to 3 to 4 etc. For this data set rows 2-7 are "1", 8-16 are "2", 16-22 are "3". Using some formulas on the sheet I plan on looping thru the ranges, i =1-3 in this example. lrow is the last row of data on the sheet (lrow = 22). Range("P1") is the max # in the helper column.

In the code below StartRow works just fine, the EndRow is what's not working. When i=1 StartRow should be 2, and EndRow should be 7, but it keeps returning 22 or sometimes 21. I'm not sure how to get EndRow= to 7, then 16, then 22. Any suggestions on how to fix this issue?

   For i = Range("o2").Value To Range("P1").Value


    '----------loop check for multiple BOMs

    Dim StartRow As Long, EndRow As Long
    With Sheets("BOM Load")

        StartRow = .Range("o1:O" & lrow).Find(what:=i, after:=.Range("o1")).Row
'   EndRow = .Range("o2:O" & lrow).Find(what:=i,after:=.Range("o2"),searchdirection:=xlPrevious).Row
    EndRow = .Range("o1:O" & lrow).Find(what:=i, searchdirection:=xlPrevious).Row

    End With

Upvotes: 0

Views: 1777

Answers (1)

JvdV
JvdV

Reputation: 75840

This works for me:

Sub Test()

Dim i As Long, StartRow as Long, EndRow as Long

With Sheets("BOM Load")
    For i = 1 To 3
        StartRow = .Range("O:O").Find(i, LookIn:=xlValues, LookAt:=xlWhole).Row
        EndRow = .Range("O:O").Find(i, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Row
    Next i
End With

End Sub

I've:

  • Added LookIn:=xlValues to tell VBA to not look in xlFormulas
  • Added LookAt:=xlWhole to tell VBA to prevent matches that hold part of what we looking for > 10 when we look for 1.
  • Left out After:= since by default the Find is performed from the top left cell in specified range
  • Changed .Range("o1:O" & lrow) to .Range("O:O") because we don't need it to look in a specified area of the column (unless you have data below the data you want to look through)

Note: You have also still used an implicit range reference in For i = Range("o2").Value To Range("P1").Value refering to the ActiveSheet. You might want to address that issue too.

Upvotes: 1

Related Questions