Find 0s on Sheet and Replace with Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SteveC - 29 Nov 2007 19:08 GMT

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.

Reply to this Message

JLGWhiz - 29 Nov 2007 20:59 GMT

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

> 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.
[quoted text clipped - 18 lines]
>
> thanks for thinking about this.

Reply to this Message