Fastest VB Method To Copy Formulas Down Large Range?
Dec 21, 2012
I have a range of purchase order rows, with the formulas stored in the first row (TemplateRow) which is hidden. The users may add any number of rows to this range, depending upon the number of different products being purchased.
Code:
'Copy the template row into the first newly inserted row
Rows(TemplateRow).Copy Destination:=Rows(insertionPoint)
'fill down from the inserted row down to the last new row
With Rows(InsertionPoint & ":" & NumberOfInsertedRows.Rows.Hidden = False
.FillDownEnd With
The problem is copying the formulas down to the new rows can take terribly long (minutes) in scenarios of thousands of products. Is there a faster method of copying down my formulas?
currently i'm using the consolidate function of excel inside my vba (which is working fine) but i took forever before it finished, i'm just wondering if there is an alternative way which is more faster.
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
Dim toDel(), i As Long Dim RNG As Range, Cell As Long Set RNG = Range("d2:d2500") For Cell = 1 To RNG.Cells.Count If Application.CountIf(RNG, RNG(Cell)) > 1 Then
With rngData ' clear existing filter .AutoFilter ' filter on Owner col F .AutoFilter field:=6, Criteria1:=varOwner ' check for count > 1 since heading row should always be visible If .Columns(1).SpecialCells(xlCellTypeVisible).count > 1 Then .SpecialCells(xlCellTypeVisible).Copy rngOutput End If 'clear filter .AutoFilter End With
Set rngOutput = wksSell.Range("A1")
It fails on the line highlighted in red, and from searches on this forum and google I think I need to specify more exactly which sheet it is. something like wksdata.SpecialCells.... but this doesn't work.
How can determine if a range is empty without looping it till the first value is found? On a 5x5 range a for loop is not that bad but what if its the whole worksheet? Is there a fast way to do this?
Each sheet contains 30,000 rows of data in column A, C, E, G and I. I want to find each cell of column A, C, E, G and I to another sheets column A, C, E, G and I respectively. If match found then it should return "True" in column B, D, F, H and J.
I have tried VLookup, but it takes too much of time for calculation (1,50,000 vlookup Formulas needs to calculate in each sheet with If condition) some time calculation gives wrong results.
As well as I want to do filtering on this all the data... so that time calculation gives the problems (I have used application.Calculation=xlCalculationManual)
So now I am using Range.Find method, but that loop excecutes for 30,000 times and each ittration contains 5 Find methods.
I have a loop that executes roughly 7.7 million times when my VBA program runs. Neednless to say it take a long time to run - usually a couple days.
The inner-most loop contains a line of code from way back in my early vba programming days when I knew even less than the small amount I know about programming now (and if you can follow that sentence you might be able to understand some of the spaghetti code I write :-) ).
for k = 1 to n
if Worksheets("Personell").Range("D" & Trim(Str(k))) > dtCompleted then ...
Would it be faster to use this syntax (which I just found out about):
if Worksheets("Personell").Cells(k,4) > dtCompleted then ...
It would cut out 2 functions calls, trim() and str(), so it would be faster, right?
I am almost embarassed to post the Range("D" & Trim(Str(k)) line of code because it looks so convoluted now, but that's how I learned to program, just fumbling through until it ran...
So in short, I just wanted to confirm that the cells() syntax run faster before I spend an hour editing and tested.
When I run this code I get a run time error 1004, copy method of range class failed, I have an autofilter on and it is showing 5 rows, the 5 cells in column A of sheet2 gets copied to sheet1 OK but I get the error?
I tried searching for code for each part of the task separately and trying to piece together multiple macros, that did something similar, but I’m not have a lot of luck, so I’ll break it down into two treads, I hope this will follow the rule of one question per post, but if I should have separated this request even farther as two treads, please let me know and I’ll be happy to comply.
I was given a spread sheet with a macro that loads data from a SQL Data base (not mine), it returns anywhere from one row to 100’s of rows data. Since it’s not my spread sheet and macro, I copied this sheet to my destination workbook. The Data loads in 7 columns starting in B4:H4, with the column headings titles: Acct#, Id#, Name, Qtr1 Totals, Qtr2 Totals, Qtr3 Totals, and Qtr4 Totals. Data cells in spreadsheet not formatted (general)
What I’m trying to do: part 1: I need a macro to copy the data only for columns Id# thru Qtr4 Totals (C5:H5 down thru the last row with data) to another sheet. The destination sheet range starts with rows B13:G13, with formulas below and to the right in columns H13, and J13:N13 (column I is blank).
The formulas below the range are a subtotal of the copied columns (D:G). They could be remove and totaled after they’re copied, so there's nothing below the copied range? Since I need the column totals as a subtotals, my thought was to have the row heading and formulas already on the destinations sheet and just insert a blank rows between as each row was copied?
part 2: As each row of data is copied from the source sheet, I need the macro to copy the formulas to the right of the copied range (H13, and J13:N13) on the destination sheet down for each new row of data copied from the source sheet
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
I am going through a large block of data with formulas, where I am selecting each cell and manually highlighting the formula and pressing F4 to sorround the cell letter calls with $, example $A$11. I am doing this so I can ultimatley copy the formulas as a group into another group of cells with screwing them up. This is very time consuming so I am looking for an improved method, possibly using vb code?
I'm working on a large set of data, so rather than read/writing lots of times to the sheet, I've followed the tip of reading a range to a variant array, processing the array, and then writing the whole thing back to the sheet (and it is indeed much much faster).
Dim vdata As Variant vdata = Range("SummaryOutput") 'do a bunch of logic Range("SummaryOutput") = vdata
To make things simpler (fewer ranges/variants to manage) I've defined a large range which includes non-data cells (blanks, labels, SUM formulas, etc..). The problem is when I read the range into variant array, it only copies the cells' values -- which means that once I write it back to the sheet, the formulas are lost.
So what I'm looking for is some way to read range into a variant whilst keeping the formulas (which I would see just being a string value in the variant array?). Does anyone know how this could be done?
I have a large workbook with LOTS of large formulas. However they dont contain $ characters before the ranges as I haven't needed to manipulate them til now. Now I am changing the sheet and I cant seem to move or copy these cells without the ranges whithin the formulas changing! Is there a way to do this? If not is there a way in VBA that I can check through selected cellls and enter a $ before all the ranges?
I am trying to calculate a matrix of array formulas that is roughly 365 x 137, or about 50,000 cells being calculated from a range of roughly 12,000 x 137. There are multiple if statements within the array formula, and then those 50,000 cells are referenced to another, but smaller matrix of array formulas (25 x 137). Each of these matrix sets is for one year, and there are three years that need calculated.
Problem: When calculating the 50,000 cells it takes roughly 2.5 hours to complete all calculations. So, for 3 years it will take roughly one work day of tying up my computer to just compile data that will then require several days of calculation/manipulation.
Question(s): Is there a way to speed this up to a reasonable amount of time (I'd even take 30 minutes at this point)? Or, is Excel simply the wrong tool to be using for this amount of data? Do I need a better machine to run these calculations? I am currently using a Dell XT3 with 2.5 Ghz i5 quad core processor.
I am accustomed to using filters to find a lot of my information in large datasets.
However, now I am trying to use formulas to return specific values. For simplicity's sake, I have included a sample below with a couple types of scenarios I am looking to solve through the use of formulas. Would this involve sub-arrays perhaps?
i have a spreadsheet with a certain data (rate of return) associated to a specific day of a year. (ex. 8-dec.-08 0.99865), and i have to calculate the rate of return (r) for every month, which is done by applying a formula (GEOMEAN) to all the rate of returns for the days of a month (ex. GEOMEAN(r[01 dec.]:r[31 dec.])).
i don't have any problems with that part. the hard part is that i have every single trading day from jan 00 to dec 08 (2000+ days), and i do not want to manually select the ranges. also, the dates do not include weekends (trading days per year = 252) which means that i cant (a) automatically determine a range or (b) automatically associate a number of days to a month (ex. oct 08 doesn't have the same amount of days as oct 07, because of the way week-ends and holidays are arranged..)
In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
I need to be able to query a large date range by a small beginning and end date range and return a count when the value is = each search criteria. i.e. - Search one year of dates from a table by Beg: 7/23/2012 to End: 10/21/2012 and return a count. The beginning and end dates are dynamic and I will need to reference the cells, i.e. B102 "Beg" B102 "End" and not a static date.
Dim myRng as Range set myRng = Range("B1:B100").Find(what:= "Symbol")
I have run this module frequently and successfully over the past several months, during which time Column B has been hidden. When I tried to run it today I got a Run-time error 91: Object variable or with block variable not set. I checked to see that "Symbol" was present in the stated range (it was), and noted that when I went to debug the Run-time error, myRng was 'Nothing'.
Now I find that if I unhide the column before the set myRng statement, the code runs without a hitch.
My question is: Is there a known restriction on the Range.Find Method that prevents its use on a hidden range?
I am running a vba code to add about 200 sheets...my code generates a sheet in one file and then pastes it in another file....after generating nad adding 38 sheets i get the error copy method of excel failed
i tried clearing the clipboard and resuming the code but that didn,t work
I'm having a little bit of trouble with my code. I basically need to copy-paste special a large workbook (50 worksheets) into a new range-valued workbook. I've defined Sheet as S, but in some instances when I use the variable "S" I received a runtime error 1004.
Please see the highlighted red "S" in the code below.
Also, if one knew of an easier method for this particular operation that would be great!
Sub PasteSpecial()
Dim W As Workbook Dim S1 As Worksheet Dim S As Sheets
