PDA

View Full Version : Macro to automatically update variable ranges?



BSU_Engr
08-12-2011, 11:13 AM
I am a newbie to the forum and to VBA. I cannot attach my Excel sheet for some reason so I will try to explain the best I can. I have a sheet that calculates size of particles vs the percent that passes through a screen size. Once I input the raw data, the percent pass values (column C) will change while the sizes (column B) will not. Right now, I have an automatic solver that calculates the 10%, 60%, and 90% passing size based off the 2nd degree polynomial trendline coefficients. The trendlines are based off a range of 3 values on both columns (capturing the size desired)
Ex.
B13 = 1.374 C13 = 45
B14 = 1.199 C14 = 12
B15 = 1.01 C15 = 5

I want the 10% so this range is good - but say the values change to where:

B13 = 1.374 C13 = 75
B14 = 1.199 C14 = 45
B15 = 1.01 C15 = 12
B16 = 0.868 C16 = 5

Now my graph and trendline do not capture the 10% range (targeting B13:B15,C13:C15)

So can I vary the range based off a min and max for Column C and match the corresponding Column B values to create a graph and trendline? More importantly, can I automatically update the trendline coefficients used for the solver?

Any help is much appreciated, thanks!

Kenneth Hobs
08-12-2011, 01:21 PM
Welcome to the forum!

You will need 5 posts before you can attach a file. This is to stop spammers. The same rule applies to the url. You could post the file to a shared site like box.net and break up the url and obscure it to fool the forum limit.

An example file is the best way to help you. The solver is probably not the best automated way to solve your problem. We may be able to figure out another approach though an example that works is good too.