I am newbie in Excel so i got limited knowledge of programming in excel. I need to perform a dice simulation. I will toss a die repeatedly until I got all six outcomes, then record the numbers of tosses.
I tried to use the function RANDBETWEEN() in to simulate the toss of the die but I don't know how to stop my simulation.
For example, I can generate a sequence like this:
1 3 5 3 4 3 2 1 2 3 6 5 6
but I need to stop at the 11th generation since I achieve all possible outcomes (1,2,3,4,5,6) there.
see attached doc. The graph is showing the overall % from the table above. However months april-december are blank although they have a formula in them. This is causing the graph to show thes months as 0%.
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.
Has ever created a spreadsheet that used Monte Carlo Simulation?
I've been able to create one that works using the random number generator in conjunction with discrete probability distributions - that's fairly easy, just assign a probability to each discrete value in the distribution and then decide whether or not to select it using a randomly generated number.
However, I want to perform a Monte Carlo simulation that uses a continuous (not discrete) probability distribution, but how I would use the random number generator to then select a value from the distribution based on its probability of occurance.
I am trying to automate a simulation with the crystal ball add-in. Specifically the simulation will go through a set of workbooks in a directory - run the simulation (monte carlo x1000) and extra the "trial values" data in another spreadsheet.
I have tried to find crystal ball documentation but I'm not having any luck.
Im setting up a fixed hedging simulation using excel and want to use solver to obtain a maximum profit. I have taken historical stock prices and then predicted future prices using the random walk process. To create the hedge I am using european calls and puts in both long and short positions. The simulation has been set up so that the options are being brought/sold when the historical data ends and then sold/payed when the predicted data finishes.
I am letting solver change the values of the strike of each of the options and also the amount purchased/sold for each of the options too! My constraints are that all the strike prices have to be >= 0, all the amounts of each particular option has to be >= 0 and that profit has to be >= 0. It is worth noting that as well as the values already mentioned, d1 and d2 values are also being calculated on the sheet which are then used to calculate the value of the options being used (using Black-Scholes model), this value is then given a realistic spread value and then 3% is added on as the brokers fee. All of the values mentioned are being taken into consideration when caluclating profit.
This simple coding should repeat this four times: first ask user to input data in Textbox2.Text and then Textbox3.Text until user presses Cancel. --> x 4
But it don't seem to exit the Do Loop
For j = 1 To 4
i = 1
Data1 = TextBox2.Text Cells(i, j).Value = Data1
Do
n2 = TextBox3.Text
If Cells(i, j).Value <> "" Then Do i = i + 1 Loop Until Cells(i, j).Value = "" End If
In my main macro I have a line that makes it jump into a timer subroutine that starts the main macro every 30 seconds. However, I also have a line in the main macro that makes it jump into another subroutine at 2pm everyday that sends out emails. The problem is that if the timer starts the main macro again before the sending emails subroutine finishes, then it will only finish sending a portion of the emails before it jumps back into the main macro. Is there any way to stop my main macro once it recognizes that it needs to jump into the email sending subroutine, or a way to at least stop the timer subroutine?
the part of my code the jump into the sending emails subroutine and the timer subroutine look like this so far:
If timevalue(now()) >= timevalue("14:00:00") And timevalue(now()) < timevalue("14:00:31") _ Then DAILY_REPORT End If StartTimer
A good number of years ago I used a line of code at the beginning and the end of a macro to keep the spreadsheet from moving until the macro was finished. At the close it moved if a movement was necessary.
how do i stop a sheet from being printed and add an error message/ dialogue box saying this sheet cannot be printed, view only etc etc. this to appear when they goto the print option in the menus.
I am using a code to transfer data from one sheet to another using the VBA below. The only problem I am having is it is copying the conditional formatting as well which I don't want it to do as it is already set up in the sheet it is copying to. How can I stop this?
Code: Sub MONTHLY_UPDATE_TextBox2_Click()Dim rRehab As Long, i As Long Dim wsRehab As Worksheet Set wsRehab = Worksheets("AUG")
I have event procedures for the following form events: Before update, After Update, On Open, On Load and On Activate and I have added a breakpoint to every event.
However, despite breakpoints in all these pieces of code the form opens and closes without the code stopping to allow me to debug by manually stepping through the code.
Have I done something fundamentally wrong, I'm still a newbie at VBA?
If Cells(i, 1).Value = Range("A1") Then Cells(i, 1).Select
End If Next i
End Sub
And I was wondering if there was a way to end the Sub as soon as it gets to the Cell with the “A1” value in it as opposed to just continuing through all the rows to the end of the sheet even after the right value has been found?
I have the following VBA in the attatched spreadsheet (seperated so I could upload)
The only problem that I have is that I cannot stop the loop, it constantly keeps going round in a circle.
What im wanting to do is when the Date checked is the End date and the End Time has been checked, to move onto the next row and do it again until it gets to row 40.
Sub Calculate() Dim CellNo As Integer Dim CurrentDate As Date Dim StartDate As Date Dim EndDate As Date Dim theDate As String Dim c As Range Dim i As Integer Dim addDate As Integer Dim DaysHoursSick As Date Dim StartWorkTime As Date Dim EndWorkTime As Date Dim endmarker As String
I've only got this VBA working really under Wednesday so I can make sure it is right before adding it onto the other dates,
I'm using Application OnTime to fire a procedure every 30 mins. It works great, but the workbook tries to reopen itself when i close it (but not if i close excel entirely). I assume this is because I haven't stopped it anywhere. So I put a call to a procedure that has: Application.OnTime RunWhen, cRunWhat, , False
In the "Workbook_BeforeClose" event. This doesn't solve the problem. What am I missing?
Private Sub Workbook_BeforeClose(Cancel As Boolean) StopTimer End Sub
Private Sub Workbook_Open() StartTimer End Sub
(module1)
Public RunWhen As Double Public Const cRunIntervalSeconds = 1800 ' thirty mins Public Const cRunWhat = "dothis"...............................
I am trying to stop this code from re-occurring at the end of the spreadsheet. There are two constants that will always appear as text, one is "blank" and the other is "Grand Total". I would prefer to use "Blank" if possible. The spread sheet varies in size from day to day. I have a range right now in use up to A700. I did this because when I originally put in a column formula, it ran until 20000 + rows down. Can someone please provide me with a formula that stops this code once it reaches the bottom of my spread sheet?
I have a textbox in a user form that I'm using as a way for someone to view what I've written but not for them to write in. Is there a way to easily disable them from writing in the text box?
I'm having a problem with the screen flickering whenever a different cell is selected due to a ComboBox. I searched the forums and wasn't able to find any code that helped my problem. The fact that I wouldn't know where to insert the proper code to fix it even if I could find it doesn't help. That's why I assume the "optional code" listed at the end that I found online does not work properly because it probably isn't in the right spot. Here's the
Is there a way in which I can get excel not to run any formulas until I run some sort of command or click a button in order to tell it to?
For example, I have two sheets, one sheet has raw data entered manually into each cell, the other sheet has a large amount of formulas to calculate totals from this raw data. But every time a number is entered into a cell in the raw data sheet Excel says 'Calculating Cells', I want to be able to enter all my raw data, then go to my totals sheet and execute all my formulas.
I've modified some code to perform a loop which creates a new worksheet for each item in a pivot table Page By field (EmpName), copying and pasting values for the displayed pivot table with each name selected. Everything is working fine until it gets done with the last item in the list, at which point I get a Run Time Error 1004, "Unable to Get Properties of the Worksheet Class".
I want the MsgBox to pop up at the end, stating how many sheets have been added.
Sub Loop_PivotItems() 'Turn off screen updating Application.ScreenUpdating = False 'Store the sheet with the Pivot Table Piv_Sht = ActiveSheet.Name 'Loop through every PivotItem in the PageField (Filter) of the Pivot Table
I have a very simple userform that i'm using to allow users to name their 'location'
Private Sub CommandButton1_Click() ActiveWorkbook.Unprotect If Me.TextBox1.Value = "" Then Beep MsgBox "Please give your location a name" TextBox1.SetFocus Else Sheets("Template").Visible = True Sheets("Template").Copy Before:=Sheets(1) ActiveSheet.Name = TextBox1.Value Sheets("Template").Visible = False
ActiveWorkbook.Protect Unload Location
End If
End Sub
As i'm sure you've already guessed, there is nothing stopping a user from trying to rename the new sheet with an existing name. Is there anyway of checking the entry and workbook for duplicated enteries?
Everytime that I insert a row into a worksheet which a number of formulae refer to, those formulae get updated automatically in such a way that it is not in my favour. The formulae refer to rows 2:2000 on a number of different columns. Adding a new row shifts the references to 3:2001. I presume that I can stop this from happening
Example:
= SUMPRODUCT(('List of DD Donors'!E2:E2000)*('List of DD Donors'!F2:F2000='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")))
changes to...
=SUMPRODUCT(('List of DD Donors'!E3:E2001)*('List of DD Donors'!F3:F2001='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")))
Response = MsgBox("Have you considered all the items on the checklist?", vbYesNo)
If Response = vbYes Then
Go_To_Checkbox
I want the routine to stop without saving (after running Go_To_Checkbox) if the answer to "Have you ticked the checklist checkbox?" is 'No'. I know the 'Stop' function will do it but I just want to exit the routine without saving, rather than the routine stopping and calling up the Visual Basic editor.
I have the following macro that runs every time the system clock's second changes to 07 seconds. I however want it to stop running at 09:47 am. I have the code to stop the timer also (given below) but I am unable to stop the loop at 09:47 am. How do I do that?
VB: Sub Timer()With Workbooks("book3.xlsm").Sheets("Sheet1") Range("B10:E10").ClearContents End With Application.OnTime DateAdd("n", 1, CDate(Left(Now, Len(Now) - 2) & "07")), "Timer" End Sub
