i want a cell to automatically increase its value by 1 each time another
(predetermined) cell shows a value of zero, is this possible ?, if so,
how would i go about it ?.


--
dadad
------------------------------------------------------------------------
dadad's Profile: http://www.excelforum.com/member.php...o&userid=16712
View this thread: http://www.excelforum.com/showthread...hreadid=320480

"dadad" wrote in message
...

i want a cell to automatically increase its value by 1 each time another
(predetermined) cell shows a value of zero, is this possible ?, if so,
how would i go about it ?.


--
dadad
------------------------------------------------------------------------
dadad's Profile:
http://www.excelforum.com/member.php...o&userid=16712
View this thread: http://www.excelforum.com/showthread...hreadid=320480


HI

Here is a simple but effective demo of some code which might assist you

Sub ADJUSTCELL()

Dim inc As Integer

Range("a1") = ""
Range("b1") = ""

For x = 1 To 1000
Range("b1") = Int(Rnd() * 1000)
If Range("b1") = 0 Then inc = inc + 1


Range("a1") = inc

Next

End Sub


Best N10 Beginner

This code should be just about plug and play. I assume you can get int the
VBA window (Alt F11). Paste this code into the sheet you want to increment.
You will need to fix the cell references in this code. "$A$2" where you are
testing for 0 and ("B2") where you are incrementing the result...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" And Target.Value = 0 Then
Sheets("Sheet1").Range("B2") = Sheets("Sheet1").Range("B2") + 1
End If
End Sub

If you need more help just ask...

"dadad" wrote:


i want a cell to automatically increase its value by 1 each time another
(predetermined) cell shows a value of zero, is this possible ?, if so,
how would i go about it ?.


--
dadad
------------------------------------------------------------------------
dadad's Profile: http://www.excelforum.com/member.php...o&userid=16712
View this thread: http://www.excelforum.com/showthread...hreadid=320480

dadad wrote:
i want a cell to automatically increase its value by 1 each time another
(predetermined) cell shows a value of zero, is this possible ?, if so,
how would i go about it ?.

'each time' needs narrowing --
'each recalculation' or 'each time any data is entered' or
'each time a1 goes from non-zero to zero' or ..
This assumes B1 gets incremented when 0 is entered into A1:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not (Intersect(Range("a1"), Target) Is Nothing) Then
If IsNumeric(Range("a1")) Then
If Cells(1, 1) = 0 Then Cells(1, 2) = Cells(1, 2) + 1
End If
End If
End Sub