Filling zeros between zeros

[johngio]

Hi all,

I have 2 columns as follows:

1200___1800
0______0
1200___1800
1200___1800
1200___1800
1200___1800
0______0
1200___1800

And I want to make the numbers between the zeros zero also (so it looks like:
1200___1800
0______0
0______0
0______0
0______0
0______0
0______0
1200___1800

I created the following macro:

Sub replace()

Dim iRow As Integer, iCol As Integer
For iRow = 16 To 279
For iCol = 17 To 18
If Cells(iRow, iCol) <> 0 And Cells(iRow - 1, iCol) = 0 Then
Cells(iRow, iCol) = 0
Else
Cells(iRow, iCol) = Cells(iRow, iCol)
End If
Next iCol
Next iRow

End Sub

The problem I am having is everthing below the first zero becomes zero (which makes sense as it would see any non zero with a zero above and make it a zero).

Any ideas how to alter this macro to make it look for the first occurence of a zero value, and then make everthing in the 2 columns zero until it hits the next zero, and then leave it unchanged?

Cheers

John

 

[acw]

John

Try

Sub ddd()
  Dim MakeZero As Boolean
  MakeZero = False
  
  For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    If ce.Value = 0 Then MakeZero = Not (MakeZero)
    If MakeZero Then
      ce.Value = 0
      ce.Offset(0, 1).Value = 0
    End If
  Next ce
End Sub

Tony

 

[johngio]

Hmmm.

Not working. I tried setting it to the first cell in the list (Q16) but for some reason it zeros the cells above.

What does the Makezero = Not (Makezero) do ?

Cheers

John

 

[acw]

John

1) Where is the data you want to test.
2) What code are you using (ie how have you changed the code above)
3) MakeZero is a boolean variable. The line
makezero = not(makezero) will flip it from the existing to the opposite. So if it is currently true, it will become false; if false it will become true.


Tony

 

[johngio]

I will try mucking around with a loop within a loop. Such as
If cells(iRow,iCol) = 0 Then
keep making the next rows/columns zero until hit a zero then exit loop

 

[Macropheliac]

Maybe this:

Sub Zeros()
Dim MyCell As Range
Dim MyCell2 As Range

Set MyCell = Range("A1")
Set MyCell2 = Range("B1")

Do Until MyCell.Value = "0"
        Set MyCell = MyCell.Offset(1)
Loop

Do Until MyCell.Offset(1).Value = "0"
        MyCell.Offset(1).Value = "0"
        Set MyCell = MyCell.Offset(1)
Loop



Do Until MyCell2.Value = "0"
        Set MyCell2 = MyCell2.Offset(1)
Loop

Do Until MyCell2.Offset(1).Value = "0"
        MyCell2.Offset(1).Value = "0"
        Set MyCell2 = MyCell2.Offset(1)
Loop


End Sub

It's a little loopy, but it worked in a test.

Mac

 

[johngio]

Woohoo!!!!

Thats the one.

Thanks!