Using VBA In Patch Model To Change VBA In User Model

Jan 11, 2007

I have some Excel models out in the field with some old VBA code that is now causing problems. I regularly develop 'patch' models to make updates or repairs to user models, but need the VBA code syntax to get into a Worksheet module and change one line of VBA code.

The worksheet is named 'Forecast' and I need to change line 5 of my VBA code.

The vast majority of my users are on Excel 2000. I remember the security issues with newer Excel versions and can walk then through that, but I forgot the code syntax I need to get the job done.

View 9 Replies


ADVERTISEMENT

Change Model To Category?

Apr 13, 2014

1. In column A, I have models, in column B, I want to place either a "printer" if the model begins with Phaser or Laser, and if it begins with WorkCentre make it MFD. Also, if the Phaser has MFP on the end, make this an "MFD" and If Color 560 or Docucolor, make this "Out of Scope". How do I read the first part of the string in column A to do this, or do I need to make a second column with this Phaser and WorkCentre broken out?

I have this code attached, but only works if I have the word Phaser or WorkCentre.

View 8 Replies View Related

Model For Payment Schedule

Aug 20, 2013

I'm currently developing a model for a payment schedule. For example, lets say a customer purchases an item from a store, but the store receives that payment in the following month. I'm looking to develop a dynamic model so that if I change the assumption from 1 month to another term (2,3,4+ months etc.) the model will adjust accordingly.

I've started with a formula

=IF(MONTH(H3)-MONTH(G3)=B14,G11*B13)

But of course this will not work once the term is set to larger than 1 month. With B14 representing 1 month, and G11*B13 being payment information (price * quantity).

View 2 Replies View Related

Spreadsheet Model & Buttons

Dec 20, 2006

I want to create a button on this spreadsheet - the button will be called z4 in the "Yr 9 ICT" sheet

it will include the following

rows Jane Bloggs 2
rows Jane Bloggs 12
rows Jane Bloggs 22
rows Jane Bloggs 32
rows Jane Bloggs 33
rows Jane Bloggs 42
rows Jane Bloggs 44
rows Jane Bloggs 66

What do I have to do to create this.

View 10 Replies View Related

Time Model Graph

Jul 1, 2007

I have a sheet with a table of peoples hours they are doing during the week each day. I want the hours to be put into a model shading the appropriate times to show when people will be in. I have attached a sample sheet of what i want it to do which i have had to do my self. Sheet 1 has table in and sheet 2 has the model i want to be automatically filled in once ihave entered all my data in the original table.

View 11 Replies View Related

Simulation Model For Inventory

Nov 15, 2006

I am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).

ComponentMTBF(months)
115
224

Below are Company B's sales records for the past 36 months.

MonthSalesMonthSalesMonthSales...............................

View 5 Replies View Related

Formula For A Deferred Revenue Model?

Dec 27, 2013

I have a deferred revenue model. Revenue from each month earns out at an equal rate over twelve months. Sales for each month are in column B (B21) and C. I created formulas for the first year but when I get to the second year, I'm not sure what to do, because the earned premium will include values from previous year sales and current year sales.

View 4 Replies View Related

Solver With Code-Generated Model

Dec 14, 2009

Suppose that you have some data points and you want to fit a straight line to them. You write the line's equation in the cells ajacent to your data points, make the square of each difference, sum up all the squares and you have your objective function. Of course your line equation depends on two parameters, that you enter in two cells. Then you run the Excel Solver, enter your objective function as target cell to minimize, enter the range containing your parameters, set your constraints and off you go.
So far so good.

And now suppose that in order to generate your model data it is not enough to write a straight line equation in some cells, but rather to run a complete reactor model (contained in a macro). How do I couple that model-generating macro with the Excel Solver? Now, I actually found a solution. I wrote a Worksheet_change macro in the sheet containing the model parameters, which calls the model-generating code each time the parameters are chenged by the Excel Solver. This has worked fine for a simple example as the straight line one.

Now one of the two:.....

View 4 Replies View Related

Put Together A Cancel Curve On An Annual Membership Model

Nov 15, 2009

What would be the best way to put together a cancel curve on an annual membership model?

View 5 Replies View Related

Collect Model Outputs Into Summary Table?

Feb 12, 2013

I have a question around making a summary table from a model. I will try to explain this as plainly as possible.

I have a model where you can toggle 3 different inputs across two variables. The final output number of the model will vary accordingly.

I would like to create a 3X3 table with the final output for each combination of variables. Is there a way of doing this using formulas or would I need a macro?

View 2 Replies View Related

Predictive Model - Find First Expiration Date

Sep 14, 2012

I am charged with building a predictive model that can find items in a hospital inventory that will expire before they are used. The idea is simple: if I use an item every week, this item has an expiration date of 50 weeks from now, and I have 60 on the shelf, then I will likely expire 10 of the items.

The complexity is that there are multiple expiration dates. So, I want be able to look down a list of 3000 items, find the multiple entries of expiration dates, and isolate them based on criteria (select, for example, the first chronologically expiration date).

View 1 Replies View Related

Analysis Of Adding Extra Row To A Model With Lot Of Macros

Apr 27, 2014

I have recently started working on a model built by some one else and I need to add a extra row to model. The Model has lot of macros on every page. So i need to know how adding an extra row is going to affect the macros.

I understand the main problem would be that the references in the macro wont get updated which might lead the macro to malfunction.

Whats the best way to analyze which macros are getting effected.

View 1 Replies View Related

Multiple Data Sheets In One Macro Model

Feb 28, 2007

I have: + three raw data sheets (needed as 3 lots of data with 25K records in each). + I also have a model that I want to take each record from each sheet, populates some cells in a seperate calculation sheet and then gets a result - which it adds to a total (array).Finally I have + a clever macro that shows me what % of the way through I am (i.e. displays the number of record processed out of the 75K (3*25K) total)

While I have got the model to work - there is some untidy code used where I start my clever % macro for x = 1 to 75K then repeat the same instructions for running the model for each section i.e. sheet 1 : 1 to 25000, then the same instructions for sheet 2, 1 to 25000 etc etc. and then end it I want to know if there is any clever coding that will let me go from one sheet to another when there is an empty cell struck on the active sheet?

View 2 Replies View Related

Sensitivity Table For Given Return In Derivatives Model

Oct 15, 2007

I've inherited the following code, which uses solver to create a sensitivity table for a given return in a derivatives model:

Private Sub CommandButton1_Click()
'Yield Analysis
'Application. ScreenUpdating = False
x = Range("Correlation")
v = Range("SwapCur")
w = Range("DebtLTV")
Range("ValYLD").Value = 0.005
For i = 1 To 5
For j = 1 To 7
Range("ValYLD").Value = 0.005
Range("DebtLTV") = 0
a = Range("IRRung")
b = Sheets("Cashflow").Cells(35 + j, 4)
c = Range("SwapFX")
d = Sheets("Cashflow").Cells(35, 4 + i).................

It seems that the code does not cope well when negative numbers are used as inputs, as the results it gives are unexpected and incorrect. I have fiddled with various elements but to no avail. All inputs are percentages. i= 1to 5 are always positive, j 1-7 percentages, centred around "SwapFX". It is these inputs, along with "swapcur" which are now negative, and previously have not been.

View 5 Replies View Related

Model Stock Price Movements In A Column

Aug 1, 2008

I have a spreadsheet that tries to model a possible stock price movement in a column. So in column A I have all dates starting from today and in Column B a specific stock's possible stock price movemens onver the next 2 years.

So dates start at A3 and stock prices start at B3 and end at A734 and B734.

This gives me ONE possible stock movement and to get the most accurate movement, I will need to model this 20,000 times.

Therefore, I want to create a loop macro that repeat this motion 20,000 times in the columns next to A and B. So which loop effect would I need to use and how would the code look like ? This is honestly my first time attempting to use a loop macro

View 9 Replies View Related

Optimal Combination Model With Constraints & Variables

Aug 11, 2008

I need to optimize an Excel model with 5 variables each of which cam be any value from 1 to 20. The solution space, therefore, contains 3,200,000 combinations. I have a software(Palisade Risk) which can give out a random sampling of this solution space. I need to know how many combinations can be considered as a representative sample of this solution space - e.g. can it be 1%?.

View 5 Replies View Related

Creating Proper Constraints To Create Solver Model?

Apr 11, 2014

I want to take a shot at this spreadsheet exercise. Setup similar spreadsheet models.

Excel Exercise.xlsx

View 3 Replies View Related

Capturing Data: Capture Fleet No & Model From Book 1

Oct 22, 2009

find the attached file. Book 2 there is 4 columns ie,reg no,fleet no,product & model and on book 1 there is fleet no,reg no and model and what i need is to capture fleet no & model from Book 1.

View 2 Replies View Related

Pricing Model Formula Based On Units Produced

Nov 27, 2013

We are trying to work out a pricing model based on how many units we will produce. In production the price per unit will be really high when you produce a low quanity, and the price per unit will be lower with the more units you produce (but never reach zero). What we are trying to accomplish is to create a formula to build a pricing structure that accounts for this curve without having to price every possible scenario.

For example, if we have the following data where for 1 unit we charge $3 per unit to produce, for 5 units we charge $2 per units to produce, etc...:

1 units @ $3 per unit
5 units @ $2 per unit
10 units @ $1 per unit

What I need to figure out is how much it would cost per unit if we were to produce 3 units. I'm guessing it would be somewhere around the $2.40 mark give or take. Or say 8 units, would be something like $1.50 per unit.

Is there a formula that can calculate this? I've tried thinking of it as a moving average, or prorating the pricing.

View 2 Replies View Related

Formula To Work Out Maintenance In Life Cycle Model

Jul 4, 2014

I'm try to do a what IF Formula, so I can automatically add the cost into the right year for a lifecycle cost

My criteria is in column K I have either New Build or Existing and column AT I have year 12 of the lifecycle and column AZ I have year 18

And what I want the result to say is IF Column K Say New build I want the value of the first maintenance year to start in year 18 and column k say Existing I want the value of fist year Maintenance to start in Year 12

I already have this formula in the Column AT&AZ working out he value

IF(ISERROR(MROUND((AQ$1-$Q2),$P2)),"0",
IF((MROUND((AQ$1-$Q2),$P2)+$Q2) = AQ$1,$AF2,"0"))

View 4 Replies View Related

SEASONALITY Based On Names Of Months In Forecasting Model

Jul 16, 2007

I have historical sales for each month from the last 3 years. I would like to set up a seasonality adjustment for forecasting. I know that it won't be perfect and some may even suggest using regression, but I'd rather not. I'd like to do the following:

In a control sheet, list the months in chronological order beginning in cell A1; January, A2, February; etc., etc. through December.

Next, below each month, I'd like to have a factor. For example, in B1 beneath January, I'd like to be able to plug in 75%. This would say that for each January going forward, that it is 75% of the annual historical average for all periods. Whereas, say in June, it is 140%.

View 9 Replies View Related

Auto Populate Fields Based On Model Number In Dropdown?

Mar 14, 2014

I have the catalog #s of some products in column A, and I have the brand in column B, Product name in Column C, and the model # in Column D. I want to create a formula that if I select the catalog # it should autopopulate the brand in column B, product name in column C and the model # in column D!!

And I have the brand/product & model # in a different sheet!!

View 6 Replies View Related

Operational Model - Shift Start Time Plus Amount Of Hours Worked In One Cell.

Feb 19, 2009

Is it possible that a cell contains both numeric and alphanumeric data and to do calculations on that?
For example: if a cell conatain the value "10a" or "8.5b" etc. Would it be possible to have a column that gives me the hours worked (the numeric value in the cell) and a line that gives me the amount of people that are working on shift "a" (the alphanumeric value in the cell).

Is this at all possible? Or does that require VBA/Macros and stuff (in which case this is posted in the wrong part of the forum )

View 5 Replies View Related

Mixing VLOOKUP And LEFT Functions (bring Up The Model Type Of A Product Based On Its Material Code)

Aug 3, 2009

I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:

View 2 Replies View Related

Calculate The Implied Volatility Which Minimizes The Sum Of Squared Differences Between The Observed Market Price And The Model Price For Each Day

Jun 9, 2008

I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.

View 2 Replies View Related

Chart Objects: Chart Object Model

Aug 31, 2006

I'm looking for the excel model object chart and class diagram. I find the model object's description in the microsoft page, however i'm liked to have the object model and the class diagram in chart or picture.

View 5 Replies View Related

Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

Dec 23, 2008

I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

View 4 Replies View Related

Add ".jpg" At The End Of Each Model Number

Dec 2, 2008

I have a whole row full with model numbers, they are all differnet in each line. i would like to duplicate this row and add ".jpg" at the end of each model number.

View 2 Replies View Related

Designing A Workbook Patch

Jan 7, 2004

I created a large workbook (let's call it Workbook X) that might not be completed yet. As a large group of colleagues use a copy of this workbook and already entered their data... I need a quick way to upgrade their copy with the changes I made in my version.

I learned before that running a macro from within one workbook could alter things in another workbook. Based on that idea... and the knowledge that I am more a designer than a programmer... I need help to bring my ideas into practice.

The idea is as follows. I record any changes made in the mother workbook into a macro. I migrate the module with the code into an empty workbook and rename it auto_open. I send the patch workbook to the colleagues and ask them to place the workbook in the same folder as the workbook X. When opened the patch workbook the code is executed; it opens the workbook X, it shuts off the use of macro's in workbook X, executes the change-code and finally saves and closes workbook X as well as the patch workbook.

Need isn't? Well, is it possible to create such a patch workbook and how can this be done? Other suggestions are welcome too...

View 9 Replies View Related

Exl2K User: Sheet Is Password Protected But User Can Change Color In Unlock Cells

Jun 16, 2006

find attached zip file . if you open the file then you can understand the problem.

I need to change cells color and text color as per user choice in Password protected sheet : (user is useing excl2000)

Kindly open attahced file i putted the note inside the file.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved