need help!!!

rafaelff

New Member
Joined
Nov 5, 2002
Messages
5
Hello!

I am currently modeling a heat transfer case, and now that the finite differences model is ready and works fine, I have a problem: I need to use the "Seek goal" (or something like that, it's a function called "Zielwertsuche" in German), to get the values without overflowing the program. This would be just fine, if I didn't need to do it for around 2500 cells... does anyone know about a macro or any visual basic program, or any other way excel has for helping me solve this problem? Thanks a lot in advance.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

attc

Board Regular
Joined
Oct 11, 2002
Messages
87
I'm not sure what you mean by "without overflowing the program" -- sorry if that's a silly question, but can you elaborate?

you can use VBA to do goal seeks. as a simple example, say you have data and a formula that converts celsius to fahrenheit:<HTML><HEAD><Script Langage JavaScript><!---
function ViewSource() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource = RetDeleted(HtmlSource);
document.write('<HTML><BODY BGCOLOR=#E0F4EA><CENTER><FORM><TEXTAREA ROWS=30 COLS=90%>');
document.write(HtmlSource);
document.write('</TEXTAREA></FORM></CENTER></BODY></HTML>');
}
function CopyToClipBoard() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource=RetDeleted(HtmlSource);
window.clipboardData.setData("Text",HtmlSource);
alert('Html source of above imagennhas been copied to your clip boardnnJust paste it into Message BodynnIf you cannnot paste source from clip board,nnclick [View Source] button and paste manually.');
}
function RetDeleted(targetstring) {
if (targetstring.indexOf(unescape('%0D%0A')) > -1) rcode = unescape('%0D%0A')
else if (targetstring.indexOf(unescape('%0A')) > -1) rcode = unescape('%0A')
else rcode = unescape('%0D');
i = 0;
p = '';
while (targetstring.indexOf(rcode,i) != -1) {
m = targetstring.indexOf(rcode,i);
p += targetstring.substring(i,m);
i = m + rcode.length;
}
p += targetstring.substring(i,targetstring.length);
return p;
}</Script></HEAD><BODY BGCOLOR=#E0F4EA><CENTER><FONT COLOR=#339966 SIZE=5>[HtmlMaker 2.32]</FONT></CENTER><HR><SPAN id='ForSubmit'>
Macro2.xls
ABCDEF
1SourceResultTarget
210021250
3100212100
4100212150
5100212200
6100212250
7100212300
8100212350
Sheet1
</SPAN><CENTER><HR><FORM NAME='form1'><INPUT TYPE='Button' value='Please click this button to send the source to clipbord' onClick='CopyToClipBoard();'><INPUT TYPE='Button' value='View Source' onClick='ViewSource();'></FORM><FONT COLOR=#339966 SIZE=2> This free code was written by Colo and Ivan F Moala:[HtmlMaker 2.32] - 8th Oct 2002</FONT><FONT COLOR=#339966 SIZE=2>Code mods by Ivan F Moala - 8th Oct 2002</FONT></HR></BODY></HTML>


the following VBA code when run would goal seek cells A2:A8 based on the targets C2:C8

Sub GoalSeekEg()

For Each c In Range(Range("A2"), Range("A65536").End(xlUp))

c.Offset(0, 1).GoalSeek Goal:=c.Offset(0, 2).Value, ChangingCell:=c

Next

End Sub

HTH
 

attc

Board Regular
Joined
Oct 11, 2002
Messages
87
oops.. sorry abt the formatting etc. got the usage of Colo's utility wrong and I can't seem to go back and delete/edit the post.

here's the VBA again to be clear:

Sub GoalSeekEg()

For Each c In Range(Range("A2"), Range("A65536").End(xlUp))

c.Offset(0, 1).GoalSeek Goal:=c.Offset(0, 2).Value, ChangingCell:=c

Next

End Sub
 

rafaelff

New Member
Joined
Nov 5, 2002
Messages
5
Hello attc,

Thanks for your soon answer. The goalseek function is exactly what I need, however I have never worked with Visualbasic or macros in Excel.

My specific problem is: I get a result from one side, from a finite differences calculation, and I get another result from other side, from formulas... and the point is I need both values to be the same. So, I create a new cell, that is the first one minus the second one, and then I "goalseek" the value of this cell to zero, by changing one of the values mentioned above.

Call me low-tech, but could you help me out with this? Thanks a lot again for your previous response.
 

Forum statistics

Threads
1,144,444
Messages
5,724,391
Members
422,548
Latest member
Cuprian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top