Debug problem

S

Sofia Grave

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
 
D

Dominik Petri

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.
 
S

Sofia Grave

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 said:
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 said:
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
Click to expand...
Click to expand...
 
D

Dominik Petri

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro Help 3
MAcro help 0
Goal seek - column reference 3
Correcting "Circular Action" in Worksheet_Change 2
Loop several sheets - part 4 - Dave Peterson - Goal seek 1
Do Loop Goal Seek won't End Sub 2
Goal Seek with dynamic Goal Seek 1
Goalseek - loop range 2

Top