Dim x As Integer
Dim y As Integer
Dim Snominaloc As Double
Dim Scs As Double
Dim Sirating As String
Dim Mnominaloc As Range
Dim Mcs As Range
Dim Mirating As Range
Dim Mcbrating As Range
Set Mnominaloc = Worksheets("Parameters").Range("b10")
Set Mcs = Worksheets("Parameters").Range("b22")
Set Mirating = Worksheets("Parameters").Range("b12")
Set Mcbrating = Worksheets("EL").Range("b33")......................
I required to do some Monte Carlo analysis for 1000000 simulation. I have managed to find some free code, however, the time it took to run 1000000 >30min. Is that normal? The code that it took the longest to run is following:
For i = 1 To number_of_trials Application.Calculate For j = 1 To number_of_formulas runs(j, i) = sel. Cells(1, 1 + j) Next j Next i
Is there any way I can implove this code to make it run faster? I have already tried Application. ScreenUpdating = False
I have a large worksheet (Sheet1) containing approximately 15,000 records (15,000 rows x 21 columns). I need to search through all these records, and manually decide whether a record should be copied onto another worksheet (Sheet2) or not. The code I have written works very well (as far as I can tell), but it is extremely slow (the searching takes forever).
The code is as follows:
Sub SearchAndCopy() Dim SearchRow As Long Dim LastRow As Long Dim SearchColumn As Long Dim LastColumn As Long Dim CopyToRow As Long Dim SearchString As String Dim Found As Boolean Dim Response As Integer On Error Goto Err_Execute Found = False 'Fetch Search Term Sheets("Sheet2").Select SearchString = LCase( Range("F3").Text)......................
I have a macro I run on thousands of rows of data which occasionally has incorrect line breaks that need to be brought up to the previous row. The macro works perfectly, however I'm trying to speed it up by skipping rows that do not need processing.
The macro inserts a check in column AQ to see if column A meets certain criteria. If it does not, "False" is entered in AQ, indicating the need for processing. As it runs now, the loop steps through each row of AQ to see if it needs further action.
I am trying to come up with a way for it to automatically just jump to "False" instances, skipping sometimes thousands of rows of blank cells that need no processing.
I have a problem with one of my loops, it takes about 17 seconds to do the job of calculating a simple moving average for 200 periods on 20,000 rows. However, if I do the "FillDown" function for the same type of average, it takes 1 second.
it seems like the first instance of the code is running the way i want it to run, but when it starts with the second instance, it does the first search and copy, but it seems like the nested loop is being ignored.
am i doing something wrong?
dan ========================================================== Thanks to Aaron Blood for the find_range function. i also poached the lastrow function from somewhere on ozgrid, but I cant remember the name of the poster. ==========================================================
Sub new2()
Dim Org_Area As Variant Dim Item As Variant Dim Copy_To1 As Variant Dim Cell_Ref As Variant
I think its something simple I'm missing here. I have a range of stock prices (Range("E3,E4,E5,E6")) what I'm trying to do is use for each next loop to calculate the number of shares that I could trade. and place those shares in a range of cells adjacent to its corresponding price (Range("G3,G4,G5,G6")). I think the problem is in my loop statements but I can't seem to find it . Could someone kindly show me the error in my ways.
I'm trying to finish writing the last portion of a macro created to automate my data analysis project for my research but I'm having trouble writing the last For Loop of the code. I have a target cell B25 which states a given temperature of my reaction. Once a value is entered in B25, 5 activity coefficients are calculated and displayed in cells B31,35,39,43, and 47 which I would then like to copy and paste in cells J31-J35.
However, I'd like to calculate the activity coefficients for a range of temperatures which are listed in cells J30-W30. Thus, I need to create a For loop which copies the value from J30 into B25, copies the values from B31,35,39,43, and 47 to J31-J35 and then moves to the next column replaces the value of B25 with K30 and repeats the same process until all the columns are filled.
I have 2 sheets in the same workbook -- Entry and Setup. Setup sheet contains details about each class including how many arenas are going to be used for the event. The entries need to be divided up amongst the arenas. We are dealing with around 500 entries.
Setup Sheet Class Number Number of Arenas Used Class 1
I'd like to know how I can loop through a series of frames and loop through each of the option buttons in each frame and write the value of the selected option back to a worksheet.
Here's an example of how I'm doing it now:
For Each ctl In fraWorkMode.Controls If ctl.Value = True Then ws.Range("A1").Value = ctl.Caption Exit For End If Next ctl For Each ctl In fraTransport.Controls If ctl.Value = True Then ws.Range("B1").Value = ctl.Caption Exit For End If Next ctl For Each ctl In fraTravelTime.Controls If ctl.Value = True Then ws.Range("C1").Value = ctl.Caption Exit For End If Next ctl
I'm also thinking that OFFSET is maybe a better option than specifying each cell for the collected data.
These frames are on Userforms for a survey and some of the frames contain up to twelve other frames so a nested loop approach appears to be more efficient.
I have a problem with a nested loop in my macro as Excel hangs every time i run my script. I suspect it is because my sheets have too many elements.
I am doing a software localization, and I need to translate records in column A (english) to column X (slovenian) in the same row. With each new version of the software, the number of rows changes.
I already manually did one translation (44000 rows), and now i need to do a new version translation, so I want to use my old translation (because many if not most of the elements are the same) and insert values into the new excel file.
In the new file in sheet1 I have a full column A with english words and an empty C column with slovenian words that I need to fill.
Into sheet2 I copied the old english in column A and slovenian translation in column B.
On sheet 3 i created a button that when clicked, goes though each row in sheet1.columnA, compares it to sheet2.colimnA, and if there is a match, copies the value from sheet2.columnB into sheet1.columnC. So, this should fill most of the translations I need to do, and the rest I will do manually
I am quite new to VBA so this is what I came up with:
[Code]....
The number of rows is around 44000 in each sheet. How I could optimize or even completely avoid the nested loop, because each time i click the button I need to wait for almost an hour and then Excel hangs.
is there any body kind enough to look into this code and tell me from where the loop body starts and ends and which is the loop variable and how it is being incremented or decremented. i know it is a stupid question but i admit here that i learn and manage to update my sheets only because of this forum help, as i have absolutly no idea of VBA when i started some couple of months back but know i can do little bits. thanx again for all the help i got so far and hope to get some solution this time as well
I think I just need a basic Nested For Loop code. I have a list of in column A, from A1:A537 and another list in column B, for B1:B50. I want to add a column that concatenates, each item in Column A to B1 (so 537 rows), then again each item in Column A to B2, another 537 rows, etc.. this loop is repeated 50 times, one for each name in column B.
I am trying to loop through Column Q2:Q2000, and divide the adjacent Row R2:HS2 by the value in the corresponding Q column.
ie. cells R2: HS2 should all be divided by Q2 cells R3:HS3 should all be divided by Q3 etc until R2000:HS2000 are divided by Q2000
I presume that i would require an initial loop to scroll through the column and a seperate loop to scroll through the row values, but i cannot get my head around how this is done.
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate() Dim sBook As String On Error Resume Next Application.EnableEvents = False Col = 8 ' Sets start column Do sBook = Cells(2, Col) ' References file name TaskRow = 6 ........................
which then compares the three-letter code with a list in the following format to create a new text file where data is only included if the code is on the list. Since I added the range search to the code it runs VERY slow
I'm working on a financial reporting project that should be in Access but unfortunately it must be in Excel. Some of the formula are complex and I have a UDF to calculate these values. I added the line
Application.Volatile
to each UDF but when I change the current month in a dropdown box, the UDF's do not recalculate. The dropdown box sets a period number on one of the worksheets - this same value is passed to each UDF. I tried using this code in my dropdown box :
Sub DropDown4_Change()
Application.CalculateFull
End Sub but the PC just hangs. I have hundreds (more likely thousands) of formula in the spreadsheet and the recalc is recalcing everything whereas I just want it to recalc the UDF's. I even changed all of the sumproduct formulae to array sum if formulae which sped things up - that is until I forced the full recalc on the drop down change event.
So my question is: is it possible to just recalc the UDF's on 3 worksheets when the user selects a different period in a dropdown box?
And a supplementary question : if {sum(if(...))} formula are faster than sumproduct formula, would a (well written) UDF perform faster than a {sum(if(...))} formula?
With Sheets("regrade pharm_standalone") For Each r In .Range("standaloneTerritory") If r.Value = "X101" Then r.EntireRow.Copy Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues End If Next r End With ------------------- I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
I have a time (1:08:31) that it took to travel 35km. How can I calculate the average speed of this competitors plus a number of others who recorded faster or slower time?
I managed to convert the time to seconds but when I load a simple formula to convert to KPH it never works.
The macro clears specific columns in a row when you click anywhere on the row and then hit the command button. It clears the first range and 2nd range in 2 distinct steps, and takes up to 3 seconds.
1) Current Speed 2) Current Acceleration 3) Acceleration Growth
Assuming: Current Speed=0 Current Acceleration = 0.2 (each 'turn' the current speed will increase by this much) Acceleration Growth = 0.2 (each 'turn', the current acceleration will grow by this much)
This gives a current speed over a series of 'turns' as 0.0 + 0.2 = 0.2 0.2 + 0.4 = 0.6 0.6 + 0.8 = 1.4 1.4 + 1.0 = 2.4 2.4 + 1.2 = 3.6 3.6 + 1.4 = 5.0 5.0 + 1.6 = 6.6 6.6 + 1.8 = 8.4 8.4 + 2.0 = 10.4 etc.
What I'd like to do is have a formula (or some way other than calculating each step) to tell me how many turns it would take for the Current Speed to =>X (example 100)
Basically, Turns to X speed = something clever * acceleration growth * something else very clever.
