variable counter

[samisamih]

I try to create index(n) so that whenever the value of cell A1 is different from the value of cell B1, it is added to the variable N one

The code I wrote:

public n as integer
Private Sub Worksheet_Change (ByVal Target As Range)
If Cells (1, 1) .Value <> Cells (1, 2) .Value Then
Cells (1, 2) .Value = Cells (1, 1) .Value
n = n + 1
End If
Cells (n, 3) .Value = 10
End Sub

But the problem that the variable (n) gets the value in delay, and the macro runs the last command "Cells (n, 3) .Value = 10" before the variable (n) enough to get the value then i get the error because the variable (N) is still zero, how can I solve this issue ?

when I wrote the last command "Cells (n, 3) .Value = 10" within the Conditional the macro works fine,without delay and the variable (n) get the result withount any delay:

public n as integer
Private Sub Worksheet_Change (ByVal Target As Range)
If Cells (1, 1) .Value <> Cells (1, 2) .Value Then
Cells (1, 2) .Value = Cells (1, 1) .Value
n = n + 1
Cells (n, 3) .Value = 10
End If

End Sub


but I need to use the variable (n) Outside the Conditional, any help please?




best reagards

[Paul_Hossler]

1. this is the Word Forum, not Excel

2. Please use code tags to bracket the macro -- that's the [#] icon -- and paste between the [ CODE ] and [/ CODE ]

3. I think you want to use Application.EnableEvents around the line to prevent your event handler from being called again



Public n as integer

Private Sub Worksheet_Change (ByVal Target As Range)

If Cells (1, 1) .Value <> Cells (1, 2) .Value Then
Cells (1, 2) .Value = Cells (1, 1) .Value
n = n + 1
End If

Application.EnableEvents = False
Cells (n, 3) .Value = 10
Application.EnableEvents = True

End Sub

[samisamih]

thank you its work now

only to understand the Boolean, tell me please,
is the Boolean Only works on the section between the false and the true?and prevent reset or changed value in the same section ?

[Paul_Hossler]

This is probably overly simplistic. There are many explanations of the different variable types on the internet.

http://www.dummies.com/how-to/content/standard-vba-data-types.html



1. Different types of variables are really designed for specific purposes

For example,

String is designed to hold textual data: "abcdef123456AbCdEf"

Double is designed to hold floating point numbers:123.45 or -.0000001

Boolean is designed to hold one of two special value: True or False


2. If / Then / Else statements work with Boolean

So if you say

If Cash > Debt Then
Msgbox "Spend"
Else
Msgbox "Save"
End If

you are evaluating the 'true-ness' or 'false-ness' of the question: Is my Cash more than my Debt? If it is (True) then Spend it


3. Many settings also use Booleans for 'Yes/No' or 'On/Off' to control something. For example

Application.ScreenUpdating = True