PDA

View Full Version : Show avarage of cells with zero value in selected range



nick_k
07-10-2012, 05:21 AM
I have trouble explaining my macro to take all zero cells count how many i have and show me avarage in message box.

Whats wrong ?

Sub srsadas()

Dim i, count As Integer
Dim area, cell As Range
Dim sum, avg As Double
Dim color1, color2 As Integer
Dim example As Range

i = InputBox("Give the value of i")
Set area = Application.InputBox("Select range", "Range", , , , , , 8)

For Each cell In area
cell(a + 1, b + 1).Value = 2 * i
i = i + 1

Next cell
For Each cell In area
If cell Mod 7 = 0 Or cell Mod 12 = 0 Then cell = 0
Next
color1 = InputBox("colour a")
color2 = InputBox("colour b")
For Each cell In area
If cell Mod i = 0 Then cell.Interior.ColorIndex = color2
If cell Mod i <> 0 Then cell.Interior.ColorIndex = color1
Next

Set example = area.Value = 0
sum = example.Cells.count
avg = sum / area.Cells.count
MsgBox avg
End Sub

snb
07-10-2012, 06:21 AM
Avoid using reserved VBA/Excel names to desinate variable.
In your case: do not use area, nor cell

Bob Phillips
07-10-2012, 07:58 AM
Avoid using reserved VBA/Excel names to desinate variable.
In your case: do not use area, nor cell

Nothing wrong with cell, I use it all the time, the reserved word is Cells.

Bob Phillips
07-10-2012, 08:04 AM
Sub srsadas()

Dim i, count As Integer
Dim area As Range, cell As Range
Dim color1 As Long, color2 As Long

i = InputBox("Give the value of i")
Set area = Application.InputBox("Select range", "Range", , , , , , 8)

color1 = InputBox("colour a")
color2 = InputBox("colour b")
For Each cell In area
cell(a + 1, b + 1).Value = 2 * i
Select Case True
Case cell.Value Mod 7 = 0, cell.Value Mod 12 = 0: cell.Value = 0
Case cell Mod i = 0: cell.Interior.ColorIndex = color2
Case cell Mod i <> 0: cell.Interior.ColorIndex = color1
End Select
i = i + 1
Next cell

MsgBox Application.Evaluate("AVERAGE(IF(" & area.Address & "<>)," & area.Address & "))")
End Sub

nick_k
07-10-2012, 08:08 AM
is is even worse now
IT doesn't change color of 0 value cells

And still says the same "type mismatch"

I have changed all cell name with c but there is still some error