the code doesnt work with macro event

[samisamih]

hi
I try to create index 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:

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

The problem that the variable N always zero (0)(Although the condition is met) and is not updated according to what is written which prevents the execution of the last command because the variable N is zero.

in Regular model code works fine and the variable is added to it at a time that the condition exists, but I want to work within a macro event?
any help please

[mehmetcik]

Shalom.

This will never work the way you intend it to.

Because a new macro is created whenever your worksheet changes.

And whenever the new macro is run N = 0.

------------------------------------------------------------------------------------------------------

So the trick is to create a variable N that is accessible by every macro.

So in a normal macro module create this macro:

Please Login or Register  to view this content.

PLease Note that I used N1, for some reason N cannot be used as a global variable.

realisticly. The following will also work:-

Please Login or Register  to view this content.

[samisamih]

many thanks for your help
I understand now , and I wrote this code and its works:

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 remained 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, is it possible to solve this problem ?

when I wrote the last command "Cells (n, 3) .Value = 10" within the Conditional the macro works fine,without 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?


best regards

[samisamih]

the error with the variable (n)

[mikerickson]

How about eliminating the variable N and just putting 10 in the next row.

Please Login or Register  to view this content.

[samisamih]

This code does not help me because I want only whenever there is a difference between cell A1 and cell B1 to register 10 in cell (n,c)

so (n )will count to me how many times this 2 cells(a1,b1) was difference and then to use ( n )with cell c to Perform certain conditions

[mehmetcik]

Sorry my friend.

You need to declare the Variable in a normal module.

I think you are declaring it on a sheet specific module.

And I do not think that will work.

I will test this and come back if I am wrong.

Otherwise move the line

Please Login or Register  to view this content.

To normal macro module.

[samisamih]

thank for your respond mehmetcik
even Sets the variable in normal module the result the same

[mehmetcik]

Ok I have checked.


The reason your macro is crashing is that initially N = 0 or N = "" there is no line 0 or line "" so the macro crashes.

This code works:-

Please Login or Register  to view this content.

But it is not very efficient.

it will run each time the sheet is modified.

So if for example you make b1 equal to a1 +1 then

The macro will run.

The first line will Make b1 equal to a1 and that will cause the macro to run

in the above macro range d1 will be made equal to N1 and the macro will run

then range c & N1 will be made to equal 108 and the macro will run.

So the macro will run 4 times. This is not an issue with a short program. But you could quickly crash excel in a larger program.


This version disables events so the macro is only run once.

Please Login or Register  to view this content.

[samisamih]

I do not know how to thank you my friend
The code works fine now:

Public n1 As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(1, 1).Value <> Cells(1, 2).Value Then
Application.EnableEvents = False
Cells(1, 2).Value = Cells(1, 1).Value
n1 = n1 + 1
Range("D" & n1).Value = n1
Range("C" & n1).Value = 108
End If

Cells(n1, 5).Value = 20

Application.EnableEvents = True
End Sub

many many thanks
תודה רבה

[samisamih]

only I want to ask you one question please
Is at the moment I am writing Application.EnableEvents = false It neutralizes any change is happening and prevents execution of other macros on the worksheet or cause to be missing result\value with other macros in the same worksheet

best regards

[mehmetcik]

Please edit your posts to include post tags.




It stops any macros running until you have done editing.

Then you start the macros again using:

Please Login or Register  to view this content.

If that causes you a problem. then there is another solution:-

Please Login or Register  to view this content.

The Changeflag allows you to control how many times this macro runs without affecting any other macros.

[mikerickson]

This code does not help me because I want only whenever there is a difference between cell A1 and cell B1 to register 10 in cell (n,c)

so (n )will count to me how many times this 2 cells(a1,b1) was difference and then to use ( n )with cell c to Perform certain conditions

You don't need N to count how many times the two cells are together.

Rather than "every time they equal, increase N and put 10 in row N" you could "every time they equal, put 10 in the row below the last filled row."

That fills up the same cells, eliminates the fiddling with N (and the losing N if there is a error in some other routine or you press ESC).

[samisamih]

hello mikerickson

My goal is not exactly to execute the command "Cells (n, 3) .Value = 10" ,
I wrote just for testing that the variable N does not change after the condition and work well ,
And because this is a macro event what was happening then( after met and closing the condition) the variable n is reset again (zero) and then I get the error

I think the variable Boolean in post#12 solved the problem

thank you a lot for your help

[samisamih]

to mehmetcik
thank you I think the Boolean variable work ok now with the counter I will test more and update you

best regards

[samisamih]

all is ok with the counter N thank you a lot for you help

[samisamih]

hi mehmetcik
can you help me please in one thing please
i have problem with my macro that there is slowdown in performance when I write the result of the macro in cell
my code is :

Public n1 As Integer
Public changeflag as boolean
public st as string
dim arr(1 to 10) as integer
Private Sub Worksheet_Change(ByVal Target As Range)
If changeflag = true then exit sub
changeflag = true
If Cells(1, 1).Value <> st Then
st = Cells(1, 1).Value
n1 = n1 + 1
End If

arr(n1)=*number* related to other macro
cells(n1,2).value=arr(n1)
Changeflag = false

End Sub

the problem in " cells(n1,2).value=arr(n1) " the result i get in the cell is printed in delay which cause to be miss data sometimes(its happend when there is vaery very fast update of data in the sheet-part of second)

i tried to add the command application.screenupdating=false/true at the Beginning and the end but did not help.
is there a way to display the arry value in the cell without delay data , without using msgbox .?
i need that the macro work without lossing any data. its possible?

best regards