macro help

[Guest]

Hello, I have a row of data that contains 1's and 0's in series. Example:
Twenty consecutive 1's then 100 consecutive 0's followed by 200 consecutive
1's etc. I'm trying to write a macro that will go to the last series of 1's
and run a macro and continue down the list to the end. Any help appreciated!

 

[Guest]

Hi xgirl,

Try using the code below. If I understood your description correctly this
should work. I have assumed that there is only one value (either "0" or "1")
in one cell. The code will begin in Cell IV1 and begin looking left for "1".
Then it will search again from that position to look for the first "0". The
Cell to the right of the found Cell will be the first "1" in the last
grouping of 1's. You can then add your code to start from here.

Hope this helps.

Regards,
James

Sub FindLastSetOfOnes()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rngFirst As Range
Dim rngNext As Range

Set wkb = ThisWorkbook
Set wks = wkb.Worksheets("Sheet1")
' Find the first occurence of "1". This will search from Cell IV1
' looking to the left.
Set rngFirst = wks.Range("A1").EntireRow.Find("1",
After:=wks.Range("IV1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False, _
SearchFormat:=False)

If Not rngFirst Is Nothing Then
' Find the first occurence of "0". The Cell to the right of
' this one is the first "1" in the last grouping of 1's.
Set rngNext = wks.Range("A1").EntireRow.Find("0", After:=rngFirst, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False, _
SearchFormat:=False).Offset(0, 1)
If Not rngNext Is Nothing Then
MsgBox rngNext.Address
Else
MsgBox "Not Found"
End If
End If

Set rngFirst = Nothing
Set rngNext = Nothing
Set wks = Nothing
Set wkb = Nothing
End Sub

 

[Guest]

Hi James, thank you for the code. I do however get a compile error for the
searchformat:=False ... any ideas?