Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro Help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro Help
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro Help
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 "Bob Bridges" wrote in message ... 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro Help
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? --- "Sofia Grave" wrote: 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") --- "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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |