If cell in column A is zero

[singlgl1]

I'm trying to avoid going to individual formulas to make this change so I hope there's a way to do this for the whole sheet using a code, so here goes:
I have a sheet where I'd like to instruct that if cells in Column B are zero, then all intersecting cells to the right will be zero as well, otherwise use formulas associated with these cells.

For Example,If cell B8=0, then cells C8:AE8 will equal 0.
I'd like to do this for the range B8:AE38
Hope this makes sense.

Thanks!

 

[repairman615]

Hello,

On a copy try the following,

ALT+F11 to open VBEditor >>
Keystrokes ALT - I -M to insert Module>>
Paste in the below Code >>
Close the VBE >>

On you Active Sheet,
ALT+F8>>
Select ZereSet and Run.

Here is the code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ZeroSet()<br><br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("B8:B38")<br>        <br>        <SPAN style="color:#00007F">If</SPAN> cell.Value = "" <SPAN style="color:#00007F">Then</SPAN><br>        <br>            <SPAN style="color:#00007F">GoTo</SPAN> Skipper<br>            <br>        <SPAN style="color:#00007F">ElseIf</SPAN> cell.Value = 0 <SPAN style="color:#00007F">Then</SPAN><br>           <br>            cell.Offset(0, 1).Resize(1, 29).Value = 0<br>                       <br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>Skipper:<br>    <SPAN style="color:#00007F">Next</SPAN> cell<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

-Jeff

 

[Rick Rothstein]

For a coded solution... assuming your values in Column B are constants (that is, not the result of formulas), here is a fast non-looping method to do what you want...

Sub FillWithZeroes()
  Columns("B").Replace 0, "=0", xlWhole
  On Error Resume Next
  Intersect(Columns("B").SpecialCells(xlCellTypeFormulas).EntireRow, Range("B:AE")).Value = 0
End Sub

 

[singlgl1]

Jeff,
I tried Your code and where there were zeroes in column B, it did change all other values to the right to zeroes, but if I have a value other than zero the next day in a cell that previously had a zero, I'm up the creek becausae now my formulas are wiped out.Is there a way to "dis-engage" the formula instead of wipe it out when there is a zero in column B?
Can it be set to run each time the sheet is opened?

Rick,The values in Column B are not constants,but are the result of formulas.



Thanks Guys for the responses!

 

[Rick Rothstein]

Rick,The values in Column B are not constants,but are the result of formulas.

Okay, try this still fast and non-looping version then...

Sub FillWithZeroes()
  Dim LastRow As Long, UnusedColumn As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  Cells(1, UnusedColumn).Resize(LastRow).Value = Range("B1:B" & LastRow).Value
  With Columns(UnusedColumn)
    .Replace 0, "=0", xlWhole
    On Error Resume Next
    Intersect(.SpecialCells(xlCellTypeFormulas).EntireRow, Range("C:AE")).Value = 0
    .Clear
  End With
End Sub

 

[repairman615]

Hello,

Are all these cells to be adjusted numbers?


I am thinking of adding to the present formulas (you did try on a copy, right ) like *0 to give a zero assuming all true numbers.
Although here I think knowing which rows to undo would get tricky and leads to my second...

Another thought is to have a string to be added to the formulas thereby making, as an example:
=1+2+3+4 >> "*Disabled* =1+2+3+4"

The code can be executed at Workbook Open and also Before Close, but first let's try to get a desired result in this fashion.

 

[repairman615]

Hello Rick Rothstein,

That does fill with zeros. Very neat to see that without the looping, I had no idea that something like this was even possible.

What do you think of adding to the already present formulas in those cells to "preserve and disable", then be able to undo next go around?

-Jeff

 

[Rick Rothstein]

but if I have a value other than zero the next day in a cell that previously had a zero, I'm up the creek becausae now my formulas are wiped out.Is there a way to "dis-engage" the formula instead of wipe it out when there is a zero in column B?

Ah, I just read this, so you can ignore my the code I just posted because it does not do this. For what you want, you do not need any code... you can do it with your formulas directly. Where you now have this...

={your function calls}<YOUR calls function>

change it to this...

=IF(B#=0,0,{your function calls}<YOUR calls function><YOUR calls function>)

where you would replace the '#' in the 'B#' cell reference to the row number the formula is on. This way, your formula looks at B# and if it is 0, displays 0, otherwise it displays whatever your formula does now. Note the equal sign stays outside of the function calls.

 

[repairman615]

Although I am thinking Rick has kept it simple and smartly suggested the IF() function which would be, IMO, the way to do this... here is what I got:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ZeroSet()<br><br><SPAN style="color:#00007F">Dim</SPAN> cell        <SPAN style="color:#00007F">As</SPAN> Range, _<br>    i           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("B8:B38")<br>        <br>        <SPAN style="color:#00007F">If</SPAN> cell.Value = "" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">'''Do Nothing</SPAN><br>        <SPAN style="color:#00007F">ElseIf</SPAN> cell.Value = 0 <SPAN style="color:#00007F">Then</SPAN><br>           <br>            <SPAN style="color:#007F00">'cell.Offset(0, 1).Resize(1, 29).Value = 0</SPAN><br>                      <br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 29<br>                <SPAN style="color:#00007F">If</SPAN> Right(cell.Offset(0, i).Formula, 2) <> "*0" <SPAN style="color:#00007F">Then</SPAN><br>                    cell.Offset(0, i).Value = cell.Offset(0, i).Formula & "*0"<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> i<br>                <br>        <SPAN style="color:#00007F">ElseIf</SPAN> cell.Value <> 0 <SPAN style="color:#00007F">Then</SPAN><br>            <br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 29<br>                <SPAN style="color:#00007F">If</SPAN> Right(cell.Offset(0, i).Formula, 2) = "*0" <SPAN style="color:#00007F">Then</SPAN><br>                    cell.Offset(0, i).Formula = Left(cell.Offset(0, i).Formula, _<br>                            Len(cell.Offset(0, i).Formula) - 2)<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> i<br>            <br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>    <SPAN style="color:#00007F">Next</SPAN> cell<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Should this work to satisfaction and you would like to run this when the WB opens

ALT + F11
VBEditor on the left find the VBA Projet pane. On the tree find ThisWorkbook and double click to bring up thisworkbook module>> Now paste in:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    <SPAN style="color:#00007F">Call</SPAN> ZeroSet<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Rick Rothstein]

If you want to go with my idea to fix your formulas as I indicated, but feel it might take too long, here is a macro to do it for you. This macro will surround all existing formulas in Columns C:AE with what I posted earlier. Run this macro on a copy of your worksheet first to make sure it works correctly. After you run it on your real file, you can delete it because it should not ever be run a second time (that would screw up the changes it made the first time it was run). Then save the file to lock down the changes. Note, just to be sure, I would keep a copy of the file before running the macro to be sure you don't lose anything (just in case). Okay, with those cautions out of the way, here is the macro...

Sub FixFormulas()
  Dim LastRow As Long, Cell As Range
  Const StartRow As Long = 2
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  For Each Cell In Range("C" & StartRow & ":AE" & LastRow)
    If Cell.HasFormula Then Cell.Formula = Replace(Cell.Formula, "=", "=IF(B" & Cell.Row & "=0,0,", , 1) & ")"
  Next
End Sub