macro Help

[Sofia Grave]

Hej to all

I have a question, I am trying to make a goal seek using the followed the
code. And is working, but I want to add something else. The numbers for this
macro depend on values in another sheet and when I don't have the range full
I receive a debug error. And what I want is, if in the range [b23:m33] some
cells are null, the macro jumps to next (number2).
Because when the debug appears only runs the goal seek for number1. I want
to avoid to open all the time the debug and comment the extra lines that the
debug shows.

Could some one help me?
Best
Sofia


Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, forside.Range("B6:B7")) Is Nothing Then
Exit Sub
Else
'number1
.Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44")
.Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44")
.Range("h45").GoalSeek goal:=0, changingcell:=.Range("H44")
.Range("J45").GoalSeek goal:=0, changingcell:=.Range("J44")

'number2

.Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44")
.Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44")
.Range("H45").GoalSeek goal:=0, changingcell:=.Range("H44")
.Range("J45").GoalSeek goal:=0, changingcell:=.Range("J44")

End If

End Sub

 

[Bob Bridges]

Sofia, I don't see anything in your code that has anything to do with
B23:M33, so I'm going to ignore everything except this: You have code that
loops through a range ("For Each co In Range(...)"), and you find that
sometimes a cell has a value, or a lack of a value, that causes your program
to abend. You want to teach your program how to spot that error and work
around it, rather than put you in debug mode each time. Is that right?

If so, I can think of two ways you can do it. One is to examine each cell
as part of your loop, spotting potential problems before committing yourself
to the next step. The other is to set up code to intercept or "trap" the
error and handle it yourself, then return to the code instead of going to
debug mode. I tend to prefer the first method, but either one should work.

Before we talk about how to check a cell before causing the error, can you
tell me exactly what kind of problem the cell is? I mean, is it a null value
that's causing your program to stop, or a non-numeric value, or what?

 

[Sofia Grave]

Hej Bob

Exactly.
RangeB23:M23 is here I put the values, depending on the calculation that I
am doing I have to put some mumber there. when I have a number inside that
range.
Exemple B23 is 2, the goal seek for D45 runs but then the debug window opens
because for goal seek f45 can't be calculate and so on. so depending on the
number on range B23 and M23 I have to comment the goal seek lines.

'number1
.Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44")
.Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44")
.Range("h45").GoalSeek goal:=0, changingcell:=.Range("H44")
.Range("J45").GoalSeek goal:=0, changingcell:=.Range("J44")

When I stop the debug It will not run the number2.

So what I need is more like:

if in range B23:m23 cells are null jump to number 2 and do the same.

B23 -> .Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44")

number 1 and number 2 are in different sheets inside the workbook.
for number 1 I have sheet1.Range("D45").GoalSeek
goal:=0, changingcell:=sheet1.Range("D44")
and number 2 sheet2.Range("D45").GoalSeek
goal:=0, changingcell:=sheet2.Range("D44")

I hope I make some sense now.

Thansk a lot.
Sofia Grave

Sofia, I don't see anything in your code that has anything to do with
B23:M33, so I'm going to ignore everything except this: You have code
that
loops through a range ("For Each co In Range(...)"), and you find that
sometimes a cell has a value, or a lack of a value, that causes your
program
to abend. You want to teach your program how to spot that error and work
around it, rather than put you in debug mode each time. Is that right?

If so, I can think of two ways you can do it. One is to examine each cell
as part of your loop, spotting potential problems before committing
yourself
to the next step. The other is to set up code to intercept or "trap" the
error and handle it yourself, then return to the code instead of going to
debug mode. I tend to prefer the first method, but either one should
work.

Before we talk about how to check a cell before causing the error, can you
tell me exactly what kind of problem the cell is? I mean, is it a null
value
that's causing your program to stop, or a non-numeric value, or what?

I have a question, I am trying to make a goal seek using
the followed the code. And is working, but I want to add
something else. The numbers for this macro depend on
values in another sheet and when I don't have the range
full I receive a debug error. And what I want is, if in the
range [b23:m33] some cells are null, the macro jumps
to next (number2). Because when the debug appears
only runs the goal seek for number1. I want to avoid to
open all the time the debug and comment the extra lines
that the debug shows.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, forside.Range("B6:B7")) Is Nothing Then
Exit Sub
Else
'number1
.Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44")
.Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44")
.Range("h45").GoalSeek goal:=0, changingcell:=.Range("H44")
.Range("J45").GoalSeek goal:=0, changingcell:=.Range("J44")

'number2

.Range("D45").GoalSeek goal:=0, changingcell:=.Range("D44")
.Range("f45").GoalSeek goal:=0, changingcell:=.Range("F44")
.Range("H45").GoalSeek goal:=0, changingcell:=.Range("H44")
.Range("J45").GoalSeek goal:=0, changingcell:=.Range("J44")

End If

End Sub

 

[Bob Bridges]

Well, I don't know anything about goal seek. All I can suggest is that you
want to write some code that, before you start this goal-seek thing, loops
through the cells in your range, checking each one to be sure it'll work
correctly - and if it won't, take some default action without bothering you.
Do you know how to loop through cells? Do you know how to predict whether a
cell will work correctly?