Find 0s on Sheet and Replace with Formula
 

Hi, on a sheet in the area M13:AA6000, i'd like a macro to replace all cells
that are empty or have a value of 0 with a calculated number.

For example, if in cell F500 it finds a value of 0, it calculates and pastes
as value in a new number which is the average of the two subsequent cells
(average(g500:h500)

If in cell aa5123 it finds a cell that is blank, it replaces that value with
a formula equal to average(ab5123:ac5123).

if the calculated value is an error or a zero, the value it replaces should
just be 0.

Also, this should loop starting from column aa, then column z, then column
y... to column m

IF this makes sense to anyone, could some post a code that would make this
work or at least point me in the right direction? I'm a beginnger VbA
person, but I'm pretty good at compiling snippests on the forums and making
useful macros...

thanks for thinking about this.

Find 0s on Sheet and Replace with Formula
 

Steve, I didn't test this because I didn't want to have to set up the sheet
to do it. So test it on a copy before you permanently install it. It should
go into the standard code module 1 for your VBA project.

Sub replczilch()
Worksheet(" ? ").Activate 'Replace ? with ws name.
For i = 27 To 15 Step -1
For Each c In Range(Cells(13, i), Cells(6000. i))
If c = 0 Or c = "" Then
If c.Offset(0, 1) < 0 And c.Offset(0, 2) < 0 Then
c.Value = Round(c.Offset(0, 1).Value + c.Offset(0, 2).Value)/2)
End If
End If
Next
Next i
End Sub

"SteveC" wrote:

Hi, on a sheet in the area M13:AA6000, i'd like a macro to replace all cells
that are empty or have a value of 0 with a calculated number.

For example, if in cell F500 it finds a value of 0, it calculates and pastes
as value in a new number which is the average of the two subsequent cells
(average(g500:h500)

If in cell aa5123 it finds a cell that is blank, it replaces that value with
a formula equal to average(ab5123:ac5123).

if the calculated value is an error or a zero, the value it replaces should
just be 0.

Also, this should loop starting from column aa, then column z, then column
y... to column m

IF this makes sense to anyone, could some post a code that would make this
work or at least point me in the right direction? I'm a beginnger VbA
person, but I'm pretty good at compiling snippests on the forums and making
useful macros...

thanks for thinking about this.