Filling zeros between zeros

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
John

Try

Code:
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

Board Regular
Joined
Jan 28, 2005
Messages
174
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Board Regular
Joined
Jan 28, 2005
Messages
174

ADVERTISEMENT

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

Board Regular
Joined
Aug 26, 2005
Messages
165
Maybe this:

Code:
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
 

Forum statistics

Threads
1,136,309
Messages
5,674,996
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top