#### Automatically run macro

```My name is Mike and i have a question about microsoft excel macro's.
Attached is a copy of the excel sheet im working on. Below the excel
sheet is the macro I built. Some of the cells contain given values and
some cells are calculated from formulas. Cell (G4) is my given
value...it is related to cell (C32). The point is, I plug a value into
cell (C10) and it runs through the rest of the calcs in the other
cells and gives me a value to cell (C32). I built a macro that works
as a goal seek pretty much. The macro makes cell (C32) equal to cell
(G4) and gives me the value for cell (C10). I want the macro to
automatically run if i take a guess at (C10) and it gives me a value
for (C32) not equal to (G4). And what would really help me out is if
the guess can stay in col C and the right value goes in col D.

Spray Noz Des Flow is cell (C10) and Tafter spray - Tsat is cell (C32)

Increment       0.01 (G3)
Value               25  (G4)
Tolerence          0.1(G5)

Spray Noz Des Flow %  27.05
Spray Noz Des Flow lbs/hr 1,352,500

Des Flow Upper Spray lbs/hr 450,833
Des Flow Lower Spray lbs/hr 901,667

UPPER SPRAY

Went fsh lbs/hr 5,000,000
Pent fsh psia 2500

Wspray design lbs/hr 450,833
h spray  btu/lb 450.0

Went upper spray lbs/hr 4,549,167
Pent upper spray psia 2600
Tent upper spray =B0F 900
h ent upper spray btu/lb 1381.8

h after design spray flow btu/lb 1297.8
T after design spray flow =B0F 693
Tsat =B0F 668
Tafter spray - Tsat =B0F 25

Macros

Sub calculateSize()
X =3D 0
TOLERANCE =3D Cells(5, 7).Value
Do While X < 101
Cells(10, 3).Value =3D X
RESULT =3D Cells(32, 3).Value - Cells(4, 7).Value
If RESULT < TOLERANCE And RESULT > (-1 * TOLERANCE) Then
X =3D 101
Else
X =3D X + Cells(3, 7).Value
End If
Loop
End Sub

```
 0
10/2/2007 6:28:47 PM
excel 39879 articles. 2 followers.

3 Replies
1220 Views

Similar Articles

[PageSpeed] 39

```Have you considered using a Worksheet_Change event?

http://www.ozgrid.com/VBA/run-macros-change.htm
--
HTH,
Barb Reinhardt

"michael.dellaccio@gmail.com" wrote:

> My name is Mike and i have a question about microsoft excel macro's.
> Attached is a copy of the excel sheet im working on. Below the excel
> sheet is the macro I built. Some of the cells contain given values and
> some cells are calculated from formulas. Cell (G4) is my given
> value...it is related to cell (C32). The point is, I plug a value into
> cell (C10) and it runs through the rest of the calcs in the other
> cells and gives me a value to cell (C32). I built a macro that works
> as a goal seek pretty much. The macro makes cell (C32) equal to cell
> (G4) and gives me the value for cell (C10). I want the macro to
> automatically run if i take a guess at (C10) and it gives me a value
> for (C32) not equal to (G4). And what would really help me out is if
> the guess can stay in col C and the right value goes in col D.
>
> Spray Noz Des Flow is cell (C10) and Tafter spray - Tsat is cell (C32)
>
> Increment       0.01 (G3)
> Value               25  (G4)
> Tolerence          0.1(G5)
>
>
> Spray Noz Des Flow %  27.05
> Spray Noz Des Flow lbs/hr 1,352,500
>
> Des Flow Upper Spray lbs/hr 450,833
> Des Flow Lower Spray lbs/hr 901,667
>
> UPPER SPRAY
>
> Went fsh lbs/hr 5,000,000
> Pent fsh psia 2500
>
> Wspray design lbs/hr 450,833
> h spray  btu/lb 450.0
>
> Went upper spray lbs/hr 4,549,167
> Pent upper spray psia 2600
> Tent upper spray °F 900
> h ent upper spray btu/lb 1381.8
>
> h after design spray flow btu/lb 1297.8
> T after design spray flow °F 693
> Tsat °F 668
> Tafter spray - Tsat °F 25
>
>
> Macros
>
> Sub calculateSize()
>     X = 0
>     TOLERANCE = Cells(5, 7).Value
>     Do While X < 101
>         Cells(10, 3).Value = X
>         RESULT = Cells(32, 3).Value - Cells(4, 7).Value
>         If RESULT < TOLERANCE And RESULT > (-1 * TOLERANCE) Then
>             X = 101
>         Else
>             X = X + Cells(3, 7).Value
>         End If
>     Loop
> End Sub
>
>
```
 0
10/3/2007 9:33:01 AM
```No i haven't. Whats a Worksheet_Change event?

```
 0
10/3/2007 12:59:06 PM
```The link in my first reply explains it.
--
HTH,
Barb Reinhardt

"michael.dellaccio@gmail.com" wrote:

> No i haven't. Whats a Worksheet_Change event?
>
>
```
 0
10/4/2007 9:49:00 AM

Similar Artilces:

A program is trying to automatically send e-mail on your behalf.
I tried posting this in microsoft.public.outlook.general, but no nibbles. ------------------------------------------------------------------------------------------------------ Does anybody know how to turn the "A program is trying to automatically send e-mail on your behalf." prompte off? It pops when one of my MS Access apps is emailing one of it's reports to somebody. It happens on the Citrix server that one of my MS Apps is running on, but it does not happen when the app is running on my own PC. Consequently I'm hoping for some user-configurable setting that controls...

Create multiple reports using macros
There is a report that I would like to run monthly for about 90 individuals and I want each individual to get their own report. Clearly, I don't want to run the report 90 times. I assume that I need a macro to do this, but have no experience writing macros. Currently, I have set up a make table query to identify the users that will need the report (since that list is subject to change) and the report is set up with the user as a parameter, now I just need to connect the two! Any assistance would be greatly appreciated. Thanks, Kevin Do you actually need 90 separate reports or can you ...

chkdsk
Server 2003 with a 4 drive RAID (500 Gb each drive). One of the drives went south (Western Digital Caviar Black) and when the system came back up a folder was reported as corrupted and inaccessable so I set CHKDSK to run and restarted the machine. 8 hours later it has found one error in an index but still no signs of finishing up anytime soon. Is there any way to fix these errors that doesn't involve taking a server down for an entire day? http://www.microsoft.com/windowsserversystem/storage/getstorfacts.mspx Fiction: Check disk (CHKDSK) takes too long. Fact: CHKDSK...

Macro with line graph
Dear, I have huge data with different headers. I want to plot a single line graph linked with a validation with the list of all the headers. The data in the line graph should reflect only the selected header among the list. For example, In the below example i should have a button command with the validation list of all the three headers below apples, oranges and grapes. By selecting oranges in the list, the line graph should pick only the data from colA and colC.. like wise ColA ColB ColC Cold Row1 Time Apples Oranges Grapes Row2 12:00 ...

Money 2006 constantly faults with automatic updates
Whenever I use the Internet automatic updates, money faults out and wants to send a report to Microsoft. Also my account list shows most accounts with a status of 'Update In Progress' I've done a complete reinstall after removing it and power cycling my machine. I thought it may have been something with removing 2004 and installing 2006, but got same problem Has anyone else been having this problem? I really just want to go back to Money 2004. What's the error message? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://money.mvps.org/faq f...

Automatic CC
Hello, Is there any way to create a template where a contact is automatically CC in the email. So I dont forget to cc them every time I send an email. Thanks ...

Is it ok to run POS and SQL on one PC?
I have been running POS on one PC as client and SQL database on another as server. These 2 PCs are getting old and I want to upgrade. If I am getting a faster PC, is there any downside to running both POS and SQL database on one machine? Since SQL Server is the desktop edition, it seems all that power on the server side is not necessary. No, just make sure you have a good backup routine. Rob "sammm" wrote: > I have been running POS on one PC as client and SQL database on > another as server. These 2 PCs are getting old and I want to upgrade. > If I am getting a faster P...

How well does MS Office 2003 run on Windows Server 2008, IN PRACTICE ?
Hi How well does MS Office 2003 run on Windows Server 2008, IN PRACTICE ? (!) Microsoft say it runs fine, but are there any significant problems in practice? (eps re MS Access 2003). >>> http://www.microsoft.com/windowsserver2008/en/us/supported-applications.aspx Microsoft Office Microsoft Office 2003 Service Pack 3 and Microsoft Office 2007 Service Pack 2 and later versions are supported, including individual applications comprising the Office Suite >>> With thanks Ship Shiperton Henethe hi, On 15.03.2010 13:02, ship wrote: > How well ...

Automatic axis format
Hi, I'm need to make a automatic axis format within excel. The number of rows is depending of a imported file. No problem to get the max. number of rows. But to put this number into the chart ( category axis) is more problematic. I try VB but my knowledge is at dummy level ! Waiting for your reply............. Hi, Have a look at Jon's examples of dynamic charts. http://peltiertech.com/Excel/Charts/Dynamics.html Cheers Andy jos wrote: > Hi, > I'm need to make a automatic axis format within excel. > The number of rows is depending of a imported file. > No problem to g...

macro not running in XP #2
My macro is working in NT but not working in XP.compilation error saying dll not found is coming.actually the applciation contains 3 buttons. 2 buttons out of which are used to open the common dialog box (for selecting the files destination.)and another button behind which the entire macro is placed. now the problem is in XP we are unable to even click the button (to select the path ,error message saying compilation error dll not found is arising) In tools references i have checked the vba project and few others but could'nt solve the problem.. Private Sub CommandButton1_Click() Nam...

I open the MDB, the form runs automatically
Simple question I have an MDB file with 20 files and 7 forms in it. When I open it up, the form runs immediately an access goes into some sort of protected mode. I can't view the tables, queries etc. I can see the code by pressing Alt F11 but not the data. I created a blank db and imported the mdb so I could see the tables. But how do I open the original without it running the code? When I look at the code. I don't see anything that sets properties or invokes the code automatically. There are no macros in this, just the forms and associated code with them. Any ideas Thanks Colm ...

Automatic updates, automatically disabling itself. 2nd day now onecare popped up to tell me the updates turned themselves = off. I'm thinking what malware would want to turn the updates off ? Any idea what's going on here ? Did I AUTOMATICALLY install an update = that caused this ? <keepout@yahoo.com.invalid> wrote in message news:8l7mi5tkgp1gd15fkef71ll5mrui9ukmht@4ax.com... > Automatic updates, automatically disabling itself. > > 2nd day now onecare popped up to tell me the updates turned themselves > off. Drop OneCare and install Microsoft Sec...

autofill macro #2
I am trying to make a macro that will autofill a formula to the end of a column. However, the column is different lengths in each workbook. I autofilled to the end of the column in the first worksheet, which was 1124 cells long. Then in my next worksheet, the length was 2264 cells long, and it only autofilled to cell 1124. Is there anyway that I could autofill to the "end" of each column in each worksheet? Is there anyway that excel knows when to stop? Please help!! glee Sub Auto_Fill() Dim lrow As Long With ActiveSheet lrow = Range("C" & Rows.C...

My worksheet automatically adds a fill color when I enter text.
When I type text in a blank cell, the cell automatically is highlightes with the bright yellow fill. There is no conditional formatting on this workbook. Check your VBA editor. You might have an Event Macro which does what, usually, a CF does. Try to Increase the Macro Security to its maximum level in order to eliminate the Event Macro from running... You may, also consider to "rem" all the Macro commands by adding a Preceding apostrophe to each command. Micky > When I type text in a blank cell, the cell automatically is highlightes with > the bright yello...

How do I get an automatic equal sign to begin the formula bar?
How do I get an automatic equal sign to appear in the formula bar in Excel for Microsoft 2003? This happened for me in the 2000 version and I could just click on it with my mouse pointer, but now all that appears is the function sign with no equal sign after it. It makes my job a lot more time consuming to have to type in the equal sign while building formulas. Please help, anybody? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your fav...

Having numbers automatically appear bold
Hi all, Had such great advise last time, thought I'd try again. I have a large range of cells where I will be inputing sales figures i the form of currency revenue. The range is about G-I and 1-400. Th sales data will be a comparisson of YTD 2001 - 2004 revenue results. What I hope to do is have any figure that is above \$30,000 appea automatically in bold so as to stand out as a good client. I gues there is something in the IF function to do this but I'm stumped. Any ideas? Thanks again in advance. Saxte -- Message posted from http://www.ExcelForum.com select the range you wa...

Is it possible to automatically move data entered in columns into rows?
Is it possible to automatically move data entered in columns into rows? It seems like a simple thing to do but I can't find a function anywhere. Cheers, Richard Thorneycroft Husky Products If you enter something in ONE cell it is automatically in a row and a column. So, perhaps a bit more explanation is in order. -- Don Guillett SalesAid Software donaldb@281.com "Richard Thorneycroft" <dancecommander81@hotmail.com> wrote in message news:9e2f3f75.0411160750.7003c6ab@posting.google.com... > Is it possible to automatically move data entered in columns into rows? >...

3 macros in 1 button?
I want 3 different macros to run, but I want the user to be able to press 1 button only. Do I have to nest them within each other? Joe Sub Macro3() Rows("3:2000").Select Selection.Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ActiveWindow.ScrollRow = 3 Range("B5").Select End Sub Public Sub DeleteBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ...

Macros #44
I have multiple objects (ovals) in one column of a spreadsheet. I am able to create macros with corresponding buttons that once clicked, will change the color of the first oval to either red, yellow, or green. I want these macros to apply to any of the objects in the column. Thus, I want to be able to click on any object in the column and then click the corresponding macro button that will change the color of the object to that which is desired. How do I set the macros so that they do not just apply to the first oval object, but that I can run them on any of the objects? Pese, if ...

Automatic Forward
How can I automatically forward messages received from a specific sender to another's e-mail person? Thanks, ...

CE6: How to Disable SIP popup automatically
Hi, I know SIP popup can be disabled by the following registry setting in CE5: [HKEY_CURRENT_USER\ControlPanel\Sip] "TurnOffAutoDeploy"=dword:1 But It doesn't work in CE6. How to disable SIP auto-Popup in CE6? Thanks. Eric The SIP Auto Deploy in CE is due to Windows Controls invoke SHSipPreference (exported by AYGSHELL.DLL). So the easiest way is to avoid including AGYSHELL (SYSGEN_AYGSHELL) But if excluding AYGSHELL is not an option, you may need to modify the SHSipPreference (PRIVATE\SHELL\SHELLPSL\HAVEAYGSHELL\shellpsl.cpp) to supress SIP s...

Macro-Relative Reference
Excel's help page Create a Macro provides that you should go to the "Stop Recording Toolbar" and click Relative Reference so that it is selected. When I do that, no toolbar appears and all that happens is that it stops recording. Excel 2002. TIA for any help. Baldeagle Give this a try Start recording another macro, then goto Tools>Customise and select the Toolbars tab, then check the Stop Recording checkbox And don't close it by hitting the "X" when recording a macro or you will lose it again. -- HTH Bob Phillips "Baldeagle" <Baldeag...

Automatically format charts with range of dates
What I am trying to do is I have 4 charts that are linked with data from another spreadsheet. They are basically week totals for my business and make a chart compared to say last year. What I want to do though is have an option on my report that I can enter in a specific date or a range of dates and have the charts automatically filter the new data in and change. Instead of having to go into the chart data and changing what series of data I want to have a chart for I want to be able to just enter eg. "April 12-April 18" and have the charts bring up that data to reflect the ...

Automatic Database Backup
Is it possible to right code to automatically backup a database? And if so how, or where, can I get information on this? Hi There are a few way to do this. I have windows scheuler doing this on all my databases (at 1 min past midnight each day). Open Scheduled Tasks (it's in the control panel). It's very simple and not likely to go wrong -- Wayne Manchester, England. "Ayo" wrote: > Is it possible to right code to automatically backup a database? And if so > how, or where, can I get information on this? Thanks Wayne. So I take it that, there is no way t...

Excel Macro to find instances of a word
I'm in the process of learning VBA and am having some troubles writing an excel macro to find all instances of a word (usually in conjunction with other words) and delete the row containing it essentially I'd like row 5 below to do the following: If Cell contains word *foo* OR *baz* then such that it would find individual instances of foo and baz but also foobar Although overall a rather large macro, the code surrounding 1 Cells.Sort Key1:=Range("C1") 2 3 totalrows = ActiveSheet.UsedRange.Rows.Count 4 For Row = totalrows To 2 Step -1 5 IF CELLS(R...