Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace Sheet names | Excel Discussion (Misc queries) | |||
Find/Replace Event or Find/Replace for Protected Sheet ... | Excel Programming | |||
find replace within sheet or workbook | Excel Programming | |||
trying to find #REF! in a formula to replace with a sheet name | Excel Programming | |||
Find and Replace code in Sheet modules | Excel Programming |