Improve Loop, Copy Speed

Aug 18, 2007

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)......................

View 3 Replies


ADVERTISEMENT

How To Improve The Speed Of A Macro

Mar 2, 2010

I wrote a macro that works fine, although it runs extremely slowly as if plodding along through all of the cells one at a time. I'm sure the computer is faster than that, so I would like it to chug through more quickly.

I am using some syntax that could be optimized ....

View 9 Replies View Related

Improve Code To Copy Data

Apr 7, 2009

Attached is a spreadsheet that does exactly what I need it to do but I know that the code can be written better. Perhaps some experts can clue me in to how I could improve.

There is a button on the sheet Mandatory Training that looks at the sheet CS CM and finds certain numbers, when it does it makes them red and copies them to the Mandatory Training sheet. If other lines are added to CS CM that have the same numbers and the button is clicked again, only the new entries are made red and copied to Mandatory Training.

View 10 Replies View Related

How Can I Speed Up My Loop

Mar 4, 2010

I will attach a sample of the data when I figure out how to. The original data takes about 8 or 9 second to delete the lines.

Code: ...

View 9 Replies View Related

Speed Up Loop

Apr 27, 2007

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

View 5 Replies View Related

Speed Up Nested Loop

Jul 20, 2006

how to speed up the following loop? It takes about 40 seconds.

Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual

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")......................

View 2 Replies View Related

Skipping Blank Cells To Speed Up Loop?

May 14, 2014

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.

[Code] ......

View 12 Replies View Related

Loop Vs. Simple Function, Huge Difference In Speed

Feb 21, 2010

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.

Here is the code for the loop:

View 9 Replies View Related

Speed Up Slow Copy Macro

Sep 24, 2007

i am currently using the following code to copy records from one sheet to new sheets that that are created and named in the first part of the if(). this works fine, however when i am dealing with 50,000 records it still takes 5-10min to get them all sorted. I think this although functional is horribly inefficiant, and am wondering how i might be able to speed up the process. possibly rather than check each record, then copy then paste individually to sort them, then read and select the list of cells until value changes, then copy over at once. i dont know if this would be faster or not, let me know what you think.


Sub autorec ()

Dim wSheet
Dim newSheetName As Variant Dim FNAC As Double
Dim OU As Double
Dim DS As Double
Dim CCY As String

View 9 Replies View Related

Copy Cells In Loop Based On Loop Increment Being Multiplied

Feb 7, 2008

I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,

View 3 Replies View Related

Background Colours To Improve Readability?

Dec 24, 2013

Do any of the templates/backgrounds offer the user a white background with every fifth row and column an off-white colour?

My document will be read by LOADS of people, so I want to be able to follow my eye across. A soft system like that lets someone scroll across twenty columns and see they're still on the same row. It's like writing 7 Oct 2013 instead of 07/10/2013. It makes sense whether you use UK dd/mm/yyyy or US mm/dd/yyyy. People say 'October' not 'month no 10.' That's my logic and design principle.

View 3 Replies View Related

Improve Quality Of Exported Chart

Oct 20, 2008

I was working with automatically exporting the contents of a textbox to an image file and found this post which I was able to modify slightly for my purposes.

http://www.excelforum.com/excel-prog...ml#post1982405

Is it possible to improve the quality of the output image. I need to print out the resulting image and the quality of the image this method creates is too poor for my purposes.

View 7 Replies View Related

Macro To Improve Print View

Jul 16, 2009

I am generating the chart from the following code but when it prints i.e (Mode#1 or 2 or 3 etc....) with type i.e. (GL_Weld or Bend or any other text). Printing as i.e. (Mode#1Bend or Mode#2GL_Weld). I would like some space in between (Mode#1 Bend) or (Mode#2 GL_Weld) or even put it in next line underneath it ( Mode#1 Bend).
How do i incorporate and space or enter next line? Code as follows:

View 3 Replies View Related

How To Improve VBA Numerical Error Handler

Mar 21, 2012

how to improve the following error handling strategy and code samples so as to maximise its usefulness for the purposes described.

During function calculations in VBA, such as is done by numerical integrators, numerical run-time errors may occur, such as:
Err.Number = 5 Invalid procedure call eg. LOG(-5)
Err.Number = 6 Overflow
Err.Number = 11 Division by zero

My present strategy to address these errors:

Say the variable x is used in the function calculations, and some particular value of x causes one of the above errors to occur. After the above errors are trapped, the x value is increased slightly from its initial value, and the failed calculation line is retried (Resume). On the next error trap, the x value is decreased slightly from its initial value, and the calculation tried again. This way, the initial x value is cyclically changed by adding increasing magnitude increments of alternating signs, searching for the nearest x value which will allow the function calculation to be done without error. Thus it causes the x value to swing around the initial x value in a pendulum-like manner, but with a widening swing per pass.

So far, I have the following code:

Code:

ErrorHandler:
If Err.Number = 5 Or Err.Number = 6 Or Err.Number = 11 Then
If Initial = True Then

[Code]....

View 5 Replies View Related

Improve Macro To Move Row To Column?

Nov 25, 2013

i have this macro from this webiste to move every 2nd cell to the row above...(ie B1---after macro---becomes A2) I want this macro to be able to do exactly the same but instead of having a cell, it will have a row...

Code:
Sub moveDefs()
Dim i, LastRowFrom As Integer
'Identify the last row of the rule set that contains data

[Code]....

View 4 Replies View Related

How To Reduce Formula Size/improve Efficiency

Sep 1, 2009

how to reduce formula size/improve excel efficiency?

I have a really complicated formula with lots of COUNTAs, VLOOKUPs, IFs, etc etc etc which returns a variety of values, ZEROs (for blank cells being referenced), and #NAs.

I would like to turn all ZEROs and #NAs into ""s without repeating this super long formula over and over.

I am curently using:

=IF(or(ISBLANK([superlongformula]),ISNA([superlongformula])),"",[superlongformula])

View 9 Replies View Related

Does SSD Hard Disk Or A Faster Chip Improve Excel Performance

Apr 26, 2013

If I want to improve my excel performance like ability to open multiple excel and sheets to perform calculation, should I change to a SSD or a faster intel chip? My current PC sometimes give me a black screen for my excel sheet or is unable to save certain sheets when I open too many sheets.

View 3 Replies View Related

Improve Error Handling (give A Short Explanation To The User)

Feb 18, 2007

Improve Error Handling...

i would like to improve my error handling efforts (such as give a short explanation to the user, etc.)

Private Sub btn_SaveFile() ...

View 4 Replies View Related

Loop And Copy

May 15, 2007

I have a problem with the next code and i don't know how can i solve it... i have a workbook with 8 columns and variable rows per day... This workbook has 2 sheets. What I want to do is find in the first sheet all the rows that in column E have the number 570 or 640, and after this, choose some of the columns and copy them in another sheet repeating this all the time until the last row... and copying in the next row of the other sheet, starting in the 20th...

LastRow = Range("A65536").End(xlUp).Row
Dim i As Integer, j As Integer
For i = 2 To LastRow Step 1
If Cells(i, 5) = 570 Or Cells(i, 5) = 640 Then
For j = 20 To 26
Cells(i, 2).Copy Destination:=Sheets("Final").Cells(j, 1)
Cells(i, 4).Copy Destination:=Sheets("Final").Cells(j, 2)
Range(Cells(i, 6), Cells(i, 8)).Copy Destination:=Sheets("Final").Cells(j, 3)
Next j
End If
Next i

View 3 Replies View Related

Copy Pastespecial Loop

Sep 16, 2009

The following macros will:

I'm desperate for a copy paste macros that will:

1) Copy H3:H143 (141 rows) from "Sheet 1" and place this on "Sheet 2" on D4:D144 (141 rows)

2) Copy I:3:I143 (141 rows) from "Sheet 1" and place this on "Sheet 2" on I4:I144 (141 rows)

So, in essence, it will copy every single column across on "Sheet 1", but will paste every 5 columns on "Sheet 2" .

It will then loop 33 times to continually copy and paste across columns.

View 14 Replies View Related

Loop Through Range And Copy Row

Apr 19, 2012

What I'm trying to do is open a closed workbook from an active one, then go through a range looking for cells that has a different value than "noone", and copy the rows that does have something else than "noone" into the first worksheet.

What I've got so far is:

Code:
Private Sub CmdHent_Click()
Dim vikar As Range
vikar = Range(I12, I42)

Application.Workbooks.Open Filename:="C:Documents and SettingsoybMine dokumenterook1.xlsm"
Windows("book1.xlsm").Activate
Sheets("Sheet1").Select

View 9 Replies View Related

Loop With Copy Paste

May 21, 2012

What I need is the code for a loop which copies a formula from cells E1 and E2 and then pastes it, staring at C7 and then every 32 cells down until row 20000.

View 2 Replies View Related

For Loop Copy And Paste

Oct 11, 2012

The formula attached here is in cell J4. I pulled this formula down to J26. Then copy J4:J26 and paste to M2:M26, P2:P26, until CG2:CG26. I need 26 times to paste to the range. There are even 4 columns distance between two pasting range.

How can write a For Loop to copy and paste to these ranges?

=SUMPRODUCT(--($E$4:$E$10004=RIGHT(I$1,2)),--($C$4:$C$10004=$H3),$D$4:$D$10004)

View 1 Replies View Related

Loop Through Range & Copy

Jan 2, 2007

I'm trying to find a certain word in a range in a sheet and then copy all the data from the same row into another sheet. It needs to loop so that all occurrences of the word are found and the data copied. Below is the code i have tried to use. It doesn't fall over but it doesn't work either!

Private Sub CommandButton1_Click()
Dim Class As Range
Dim Cell As Range
On Error Resume Next VBA:
Set Class = Selection.SpecialCells(xlConstants, xlTextValues)
For Each Cell In Class
If Cell.Value = "Math" Then
Cell.EntireRow.Copy '1
Sheets("Sheet 3").Range("B2").PasteSpecial '2
Application.CutCopyMode = False......................

View 2 Replies View Related

Improve Coding For Copying Existing Workbook To New Workbook

Sep 18, 2006

i came up with this..

Sub Copy_To_New_Workbook()

Application. ScreenUpdating = False

Dim strFileName As String
strFileName = Application. GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", Title:="Please select the excel file to be copied")

View 9 Replies View Related

VBA - Loop To Copy Range To Different Worksheet

Mar 15, 2014

Creating a worksheet to consolidate his financial data. I've been able to muck my way through most of the VBA code by looking at examples on the forum. However, I cannot figure out how to add the last loop or where to place it. I can copy the values from the P&L sheet to the DATA sheet but I cannot discover a way to also pull the values from the BalSht sheet to the Data sheet. I think the BalSht loop will be almost identical to the "P&L sheet loop, and be placed just below it - but I'm not successful in multiple attempts and days of trying. How can I accomplish this?

Here's the VBA code:

VB:

Sub NewISCopy()
On Error Goto errorHandler
'*************************************************************
Dim CopyCol As String
Dim PasteCol As String
CopyCol = "G"

[Code]...

Excel file attached.

FI-LOOP-5.xlsm

View 7 Replies View Related

Copy Paste Macro Loop

Dec 19, 2013

I have a financial model (attached) which calculates the final cash flows (in a calculation sheet named 9.Operations, rows 428 to 437) for a particular plot. However,the model has a list of 17 plots in the input sheet (3.Input-Plot Details). The macro must change the plot number in the 9.Operations sheet(cell G11), re-calculate the final cash flows and paste all the final cash flows for 1 to 17 plots in a single separate sheet consecutively.

View 6 Replies View Related

VBA Loop Through Each Worksheet Except Two And Then Copy Paste

Jun 25, 2014

I do have macro which populates sheets based on given list.

I want to paste all data in newly created sheets from "Template".

I do not want to loop 2 sheets ("DATA" & "Template").

Data must be pasted with format & validation. Validation exists on Template sheet only. No other sheet is referred for validation.

View 2 Replies View Related

Copy/Paste 30 Times Or Loop?

Nov 18, 2009

I've attached below a small part of my code. I am wondering if there is a way to do this, without copying and pasting something 30 times, but using a loop instead. As you can see there are a series of productsNumbers (30 in total, but this example is for 3). Each productNumber is a string that is actually a 5 digit number, so it can be an integer as well, if it has to.

I would like to code below to loop for as many products as there may be. The way that I have it right now does not work - I feel that I am missing something small or that there is no way to do this.

View 3 Replies View Related

Loop - Continues To Copy Next Column Over

Sep 25, 2011

I have Created the below macro, however I cannot figure out how to make it loop so that is continues to copy the next column over ( so go from column E:E , To Column F:F, and then To Column G:G), and paste the data into the same column (D:D or Range paste_range) everytime, and print.

Sub paster_print()
'
' paster_print Macro
'
'
Columns("E:E").Select
Selection.Copy
Application.Goto Reference:="paste_range"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="Print_Area"
Application.CutCopyMode = False
End Sub

View 7 Replies View Related







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