Getting a For Loop to work correctly on a range...

[chazrab]

This works correctly…

  For i = colnum + 1 To 105
       Cells(30, i).Value = Cells(30, i - 1).Value - Cells(18, i).Value
  Next

...but this code performs the calculation all the way out to col 105 even if there are no values in cells(30,i) and cells(18,i) when this is
not necessary and and therefore takes way too long to complete.

Desired solution: stop performing the calculation and terminate the loop when the value of the calculation is 0 for Cells(30, i).Value (Cells(30, i - 1).Value - Cells(18, i).Value = 0)

Partial but not ideal solution: Much faster because Exit For terminates the loop when the value of the equation is 0 at that column on row 30 and gives a Msgbox stating at which column the value is 0.

For i = colnum + 1 To 105
    Cells(30, i).Value = Cells(30, i - 1).Value - Cells(18, i).Value
        If Cells(30, i - 1).Value - Cells(18, i).Value = 0 Then
                MsgBox "Balance is 0 at col  " & i
            Exit For
        End If
     Next

Even though this is much faster and more elegant in its operation because the loop continues only if nonzero values exist in row 30 and terminates when a value is 0, the problem here is that previously entered values are not erased. With this basic code which is analogous to a formula in row 30 to col 105:

       Cells(30, i).Value = Cells(30, i - 1).Value - Cells(18, i).Value
       Cells(31, i).Value = Cells(31, i - 1).Value - Cells(25, i).Value

It does just that – removes any and all previous values in cells(30,i).value all the way to col 105.
It just takes way too long. Not complicated at all, if it sounds that way. I’m just attempting to explain what is happening and not happening as completely as possible.


How would I alter or rewrite a For Loop to accomplish the desired result described above to 1) terminate the loop when a 0 value in row
30 is reached and 2) remove(Clearcontents?) of all previous values in the range to col 105 ?

Any help would be greatly appreciated. Sorry for the long and possibly redundant explanation.

cr

 

[Norie]

What do you mean by 'previously entered values'?

Do you want to clear the contents of cells from the column where the calculation results in 0 up to column 105?

If so, which row do you want to clear the contents of?

 

[Fluff]

How about

   For i = colNum + 1 To 105
      Cells(30, i).Value = Cells(30, i - 1).Value - Cells(18, i).Value
      If Cells(30, i) = 0 Then
         Range(Cells(30, i + 1), Cells(30, 105)).ClearContents
         Exit For
      End If
   Next

 

[Eric W]

You could read the entire range in question in 1 step, perform the calculations in an internal array, then write back the entire range in 1 step. This would be much faster than referencing the sheet multiple times in a loop. There are also ways to evaluate worksheet formulas on a range in a single step. Someone might jump in and show how to do that.

But given your code, the easiest way to modify the existing code would be to just add a clearcontents line before your loop:

    Range(Cells(30, colnum + 1), Cells(30, 105)).ClearContents

 

[chazrab]

How about

   For i = colNum + 1 To 105
      Cells(30, i).Value = Cells(30, i - 1).Value - Cells(18, i).Value
      If Cells(30, i) = 0 Then
         Range(Cells(30, i + 1), Cells(30, 105)).ClearContents
         Exit For
      End If
   Next

...this works good - the conditions 1) clears a range, being defined from the 0 cell on row 30 onward to col. 105 and 2) only beyond nonzero cells in row 30, if I'm reading how the code operates correctly. I am now wanting to duplicate this to perform the exact same For Loop operation on row 31. Can this be done within only one For Loop ?