VBA to find and replace down a column

[spill-the-beans]

Hi

Each cell in column H of my worksheet "Data" is either a 1 or a 0. I would like to search down the column for a pattern of cells that matches 1, 0, 0, 0, 1, then change those 0s to 1s.

So the data could be:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

And the code would change it to:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

Does anyone know how I could do this?

 

[BAlGaInTl]

this may not be the best way to handle it, but I tested it, and it seems to work...

Sub ReplacePattern()
Dim i As Integer
Dim StartRow As Integer
Dim EndRow As Integer
Dim sCompare As String

StartRow = 1 '<---Change to your starting row in H
With ActiveSheet
    EndRow = .Cells(.Rows.Count, "H").End(xlUp).Row
End With

    For i = StartRow To EndRow
        sCompare = Range("H" & i).Value & Range("H" & i + 1).Value & Range("H" & i + 2).Value & _
            Range("H" & i + 3).Value & Range("H" & i + 4).Value
            If sCompare = "10001" Then
                Range("H" & i + 1).Value = 1
                Range("H" & i + 2).Value = 1
                Range("H" & i + 3).Value = 1
                i = i + 4
            End If
    Next i
    

End Sub

 

[iyyi]

Run this macro

Sub NoThreeZeros()
Dim i As Long, Lr As Long
Lr = Cells(Rows.Count, 8).End(xlUp).Row
For i = 1 To Lr
    If Cells(i, 8) = 0 And Cells(i + 1, 8) = 0 And Cells(i + 2, 8) = 0 Then
       Cells(i, 8) = 1
       Cells(i + 1, 8) = 1
       Cells(i + 2, 8) = 1
    End If
Next i
End Sub

 

[spill-the-beans]

Thank you so much for the quick reply! I've been struggling for days, and you've given me a solution that works perfectly.

It may not be the "best" way to handle it, but it's so clearly written, that I can understand and learn from it

 

[wigi]

Or, working in memory rather than interacting with cells:

Sub test()

    v = Columns(8).SpecialCells(xlCellTypeConstants, xlNumbers)
    
    For i = 1 To UBound(v) - 4
        If v(i, 1) & v(i + 1, 1) & v(i + 2, 1) & v(i + 3, 1) & v(i + 4, 1) = "10001" Then
            v(i + 1, 1) = 1
            v(i + 2, 1) = 1
            v(i + 3, 1) = 1
            i = i + 5
        End If
    Next
    
    Cells(1, 8).Resize(UBound(v)) = v


End Sub

Assumptions:

- The 1 and 0 cells are constants. If they are formulas, use xlCellTypeFormulas in the first line.
- The data in column H begin at row 1. If not edit the macro at the last line.

 

[BAlGaInTl]

Run this macro

Sub NoThreeZeros()
Dim i As Long, Lr As Long
Lr = Cells(Rows.Count, 8).End(xlUp).Row
For i = 1 To Lr
    If Cells(i, 8) = 0 And Cells(i + 1, 8) = 0 And Cells(i + 2, 8) = 0 Then
       Cells(i, 8) = 1
       Cells(i + 1, 8) = 1
       Cells(i + 2, 8) = 1
    End If
Next i
End Sub

Wouldn't this one fail if you had 100001? It would become 111101.

I do like your code better, it seems cleaner to me. But I think you would have to do this and look at the 1s on either side as well. The pattern isn't 3 0s, it is 10001.

Or at least that is the way I understood it.

I also think the code loops through cells you don't need to... if you change the three 0s to 1s, there is no need to evaluate them. It works, but it would take longer if you had a very long list.

I think modifying your code for the user, it would look like this:

Sub NoThreeZeros()
Dim i As Long, Lr As Long
Lr = Cells(Rows.Count, 8).End(xlUp).Row
For i = 1 To Lr
    If Cells(i, 8) = 1 And Cells(i + 1, 8) = 0 And Cells(i + 2, 8) = 0 And Cells(i + 3, 8) = 0 And Cells(i + 4, 8) = 1 Then
       Cells(i + 1, 8) = 1
       Cells(i + 2, 8) = 1
       Cells(i + 3, 8) = 1
       i = i + 3    
End If
Next i
End Sub

 

[BAlGaInTl]

I can't seem to edit my first post....

Depending on your desired effect, you may need i+3 or i+4

For example...

100010001 becomes 111110001 with i+4 and 111111111 with i+3