Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Debug problem
in Range [A1:D1] I insert some data by hand, but sometimes the value is zero. the .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44") is based on A1 value and so on for the other lines. My question is, when in the range [A1:D1] the value is ZERO the code stops for debug, how can I avoid this without open all the times the debug window and coment the .ranges that corresponds to Zero value in RANGE [A1:D1]? That is something that I can put in the below code? B6:B7 is what triggers the Goal seek command. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, forside.Range("B6:B7")) Is Nothing Then Exit Sub Else .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44") 'A1 value .Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44") 'B1 value .Range("h45").GoalSeek goal:=0, changingcell:=.Range("H44") 'C1 value .Range("J45").GoalSeek goal:=0, changingcell:=.Range("J44") 'D1 value End If End Sub Could someone help me |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Debug problem
Sofia,
is this just a code snippet? You use .Range() etc. but without a WITH...END WITH Also, if you use If NOT Intersect(Target, forside.Range("B6:B7")) Is Nothing then ' here goes your code End if End Sub then you don't need the "Exit Sub" - and your sub has just one exit point... Not sure why you get into debug mode if there is a zero in A1:D1 because you don't tell us what's in D45 (must be a formula though) Regards, Dominik. Sofia Grave schrieb: in Range [A1:D1] I insert some data by hand, but sometimes the value is zero. the .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44") is based on A1 value and so on for the other lines. My question is, when in the range [A1:D1] the value is ZERO the code stops for debug, how can I avoid this without open all the times the debug window and coment the .ranges that corresponds to Zero value in RANGE [A1:D1]? That is something that I can put in the below code? B6:B7 is what triggers the Goal seek command. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, forside.Range("B6:B7")) Is Nothing Then Exit Sub Else .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44") 'A1 value .Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44") 'B1 value .Range("h45").GoalSeek goal:=0, changingcell:=.Range("H44") 'C1 value .Range("J45").GoalSeek goal:=0, changingcell:=.Range("J44") 'D1 value End If End Sub Could someone help me |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Debug problem
Hej Dominik
D45 is a formula, and my code is bellow. ----------------------------------------------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, forside.Range("B6:B7")) Is Nothing Then Exit Sub Else 'Loading SolasLoading.Range("D45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("D44") SolasLoading.Range("f45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("F44") SolasLoading.Range("h45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("H44") SolasLoading.Range("J45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("J44") SolasLoading.Range("L45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("L44") SolasLoading.Range("N45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("N44") SolasLoading.Range("P45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("P44") SolasLoading.Range("R45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("R44") SolasLoading.Range("T45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("T44") SolasLoading.Range("V45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("V44") SolasLoading.Range("X45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("X44") SolasLoading.Range("Z45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("Z44") 'Unloading SolasUnloading.Range("D45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("D44") SolasUnloading.Range("f45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("F44") SolasUnloading.Range("H45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("H44") SolasUnloading.Range("J45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("J44") SolasUnloading.Range("L45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("L44") SolasUnloading.Range("N45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("N44") SolasUnloading.Range("P45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("P44") SolasUnloading.Range("R45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("R44") SolasUnloading.Range("T45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("T44") SolasUnloading.Range("V45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("V44") SolasUnloading.Range("X45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("X44") SolasUnloading.Range("Z45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("Z44") End If End Sub ---------------------------------------------------------------------------------------------- I have 3 sheets, forside, solasloading and solasunloading. the values that can be zero are in the range in solasloading and solasunloading sheets. and I fill the range by hand. and that range has to do with the number of segments I am currently working, so if I am working with a section with 4 segments I fill the sizes in a1,b1,c1,d1 and the rest is zero. and what happens is that the application will give me a debug on SolasLoading.Range("L45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("L44") and if I say end to the debug window, the code unloading will not run. If I say debug I have to coment the extra lines that correspondes to zero values. I want to find a way to avoid this debug process. and if numbers in range [a1:m1] are zero.... it jumps to second part, unloading... and keep working... no debug window. thanks "Dominik Petri" wrote in message ... Sofia, is this just a code snippet? You use .Range() etc. but without a WITH...END WITH Also, if you use If NOT Intersect(Target, forside.Range("B6:B7")) Is Nothing then ' here goes your code End if End Sub then you don't need the "Exit Sub" - and your sub has just one exit point... Not sure why you get into debug mode if there is a zero in A1:D1 because you don't tell us what's in D45 (must be a formula though) Regards, Dominik. Sofia Grave schrieb: in Range [A1:D1] I insert some data by hand, but sometimes the value is zero. the .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44") is based on A1 value and so on for the other lines. My question is, when in the range [A1:D1] the value is ZERO the code stops for debug, how can I avoid this without open all the times the debug window and coment the .ranges that corresponds to Zero value in RANGE [A1:D1]? That is something that I can put in the below code? B6:B7 is what triggers the Goal seek command. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, forside.Range("B6:B7")) Is Nothing Then Exit Sub Else .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44") 'A1 value .Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44") 'B1 value .Range("h45").GoalSeek goal:=0, changingcell:=.Range("H44") 'C1 value .Range("J45").GoalSeek goal:=0, changingcell:=.Range("J44") 'D1 value End If End Sub Could someone help me |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Debug problem
Sofia,
sorry, but it's hard to tell what's wrong without seeing the workbook :-( But you can make your code more readable by changing it to: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, forside.Range("B6:B7")) Is Nothing Then 'Loading with SolasLoading .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44") .Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44") ' etc. end With 'Unloading With SolasUnloading .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44") .Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44") ' etc. End With End If End Sub Hope this helps (at least a little), Dominik. Sofia Grave schrieb: Hej Dominik D45 is a formula, and my code is bellow. ----------------------------------------------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, forside.Range("B6:B7")) Is Nothing Then Exit Sub Else 'Loading SolasLoading.Range("D45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("D44") SolasLoading.Range("f45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("F44") SolasLoading.Range("h45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("H44") SolasLoading.Range("J45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("J44") SolasLoading.Range("L45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("L44") SolasLoading.Range("N45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("N44") SolasLoading.Range("P45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("P44") SolasLoading.Range("R45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("R44") SolasLoading.Range("T45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("T44") SolasLoading.Range("V45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("V44") SolasLoading.Range("X45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("X44") SolasLoading.Range("Z45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("Z44") 'Unloading SolasUnloading.Range("D45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("D44") SolasUnloading.Range("f45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("F44") SolasUnloading.Range("H45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("H44") SolasUnloading.Range("J45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("J44") SolasUnloading.Range("L45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("L44") SolasUnloading.Range("N45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("N44") SolasUnloading.Range("P45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("P44") SolasUnloading.Range("R45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("R44") SolasUnloading.Range("T45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("T44") SolasUnloading.Range("V45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("V44") SolasUnloading.Range("X45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("X44") SolasUnloading.Range("Z45").GoalSeek goal:=0, changingcell:=SolasUnloading.Range("Z44") End If End Sub ---------------------------------------------------------------------------------------------- I have 3 sheets, forside, solasloading and solasunloading. the values that can be zero are in the range in solasloading and solasunloading sheets. and I fill the range by hand. and that range has to do with the number of segments I am currently working, so if I am working with a section with 4 segments I fill the sizes in a1,b1,c1,d1 and the rest is zero. and what happens is that the application will give me a debug on SolasLoading.Range("L45").GoalSeek goal:=0, changingcell:=SolasLoading.Range("L44") and if I say end to the debug window, the code unloading will not run. If I say debug I have to coment the extra lines that correspondes to zero values. I want to find a way to avoid this debug process. and if numbers in range [a1:m1] are zero.... it jumps to second part, unloading... and keep working... no debug window. thanks "Dominik Petri" wrote in message ... Sofia, is this just a code snippet? You use .Range() etc. but without a WITH...END WITH Also, if you use If NOT Intersect(Target, forside.Range("B6:B7")) Is Nothing then ' here goes your code End if End Sub then you don't need the "Exit Sub" - and your sub has just one exit point... Not sure why you get into debug mode if there is a zero in A1:D1 because you don't tell us what's in D45 (must be a formula though) Regards, Dominik. Sofia Grave schrieb: in Range [A1:D1] I insert some data by hand, but sometimes the value is zero. the .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44") is based on A1 value and so on for the other lines. My question is, when in the range [A1:D1] the value is ZERO the code stops for debug, how can I avoid this without open all the times the debug window and coment the .ranges that corresponds to Zero value in RANGE [A1:D1]? That is something that I can put in the below code? B6:B7 is what triggers the Goal seek command. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, forside.Range("B6:B7")) Is Nothing Then Exit Sub Else .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44") 'A1 value .Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44") 'B1 value .Range("h45").GoalSeek goal:=0, changingcell:=.Range("H44") 'C1 value .Range("J45").GoalSeek goal:=0, changingcell:=.Range("J44") 'D1 value End If End Sub Could someone help me |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Breakpoint debug problem | Excel Programming | |||
help with debug | New Users to Excel | |||
Debug Problem | Excel Programming | |||
help with debug | Excel Programming | |||
Debug problem | Excel Programming |