PDA

View Full Version : another code apart from ( target.address)



samisamih
04-28-2015, 05:55 AM
i write the following code , and i get in the range c1 the result 95 intead 1

the code:


Private Sub Worksheet_Change(ByVal Target As Range)

If Cells(1, 1).Value <> Cells(1, 2).Value Then


Cells(1, 3).Value = Cells(1, 3).Value + 1
Cells(1, 2).Value = Cells(1, 1).Value

End If

End Sub


is it possible to solve this problem without to using ("application.enableevent") , is there other soluation please?

best regards

Jan Karel Pieterse
04-28-2015, 07:34 AM
See: www.jkp-ads.com/articles/noevents00.asp (http://www.jkp-ads.com/articles/noevents00.asp)

samisamih
04-28-2015, 07:58 AM
thank you alot its work now , i have another code that the event that i need to disable it (n1) at the end of sub not at the beginning , and what explained in the site doesnt help to solve,(when i write the n1 at the beginning its worked by what explained in the site) but i need it at the end ? how can i solve it?
the code :

Public n1 As Integer
Public st As String
public flag as boolean
Dim arr(0 To 10) As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
conditions.............
conditions.........
arr(n1)=cells(6,4).value-cells(5,4).value

flag=true
if flag=true then exit sub
If Cells(1, 1).Value <> st Then
st = Cells(1, 1).Value
n1 = n1 + 1 - the problem that when the macro arrive To this point the macro return To calculate again the "arr(n1)" instead To continue To the End sub.
End If
flag=false
End Sub

Jan Karel Pieterse
04-28-2015, 08:07 AM
I'm not sure I understand. The event routine does not change any cells and hence cannot call itself. So either something else is triggering the change event or you are changing more than one cell at the time?

samisamih
04-28-2015, 08:26 AM
this code work ok (the n1 calculated at the begging and then i use arr(n1)):

Public n1 As Integer
Public flag As Boolean
Public st As String
Dim arr(1 To 10) As Intege
Private Sub Worksheet_Change(ByVal Target As Range)
If flag=True Then exit Sub
flag=True
If Cells(1, 1).Value <> st Then
st = Cells(1, 1).Value
n1 = n1 + 1
End If
arr(n1)=cells(6,4).value-cells(5,4).value
flag=False
End Sub




this code doesnt work ok (the "n1" calculated at the end ? ) :


Public n1 AsInteger
Public st AsString
public flag as boolean
dim r as integer
Dim arr(0 To 10) AsInteger
PrivateSub Worksheet_Change(ByVal Target As Range)
arr(n1)=cells(6,4).value-cells(5,4).value
if r>0 then
msgbox("flag=true")
end if
flag=true
if flag=true then exit sub
If Cells(1, 1).Value <> st Then
st = Cells(1, 1).Value
n1 = n1 + 1 - the problem that when the macro arrive To this point the macro return To calculate again the "arr(n1)" instead To continue
EndIf
flag=false
End Sub


I moved it down(the n1) because sometimes happens that the cell (1,1) and cell (6,4) changed together and then i will lose the last calculation of the arr before the n1 added to it 1.

ie the index(n1) in the first code work ok and added to it 1 when the condition met (after you tell me to use the boolean flag).
but the n1 at the second code when the condition met added to it more than 1 , (the problem that i was have in the first code before i use the flag boolean)

is there away to solve this problem?

Paul_Hossler
04-28-2015, 09:02 AM
this problem without to using ("application.enableevent") ,

why don't you want to use the features that are built in to Excel and is it ALWAYS A1 that you're checking?




Private Sub Worksheet_Change(ByVal Target As Range)

If Cells(1, 1).Value <> Cells(1, 2).Value Then

Application.EnableEvents=False
Cells(1, 3).Value = Cells(1, 3).Value + 1
Cells(1, 2).Value = Cells(1, 1).Value
Application.EnableEvents=True

End If

End Sub

samisamih
04-28-2015, 09:08 AM
this application doesnt work in my sheet , so i declare a boolean variables ,
i also used it without help . can you please take alook at my previous post #5 if you
can help me to solve the problem

Paul_Hossler
04-28-2015, 09:49 AM
Post a COMPLETE example workbook and a COMPLETE description of what you're trying to do

Jan Karel Pieterse
04-28-2015, 09:11 PM
@Paul: I was going to ask precisely the same :-)

samisamih
04-28-2015, 09:43 PM
its work now for me

many thanks for your help