Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to enter many formulas
Here's my situation. I've got a database query filling column B-F of a worksheet (no blanks with a variable number of rows (but usually many rows). I'm trying t determine the quickest method to fill column A with a formula tha depends on the row's column B value. The quickest I have so far is: Code ------------------- Dim LastRow As Integer LastRow = Worksheets("Data").Range("B1").End(xlDown).Row Do While counter < LastRow Worksheets("Data").Range("A2").Offset(counter, 0).Formula = DataIdentifier Counter = Counter + 1 Loo ------------------- But it then occurred to me a copy and paste might be quicker so I' trying to get this working. Code ------------------- Dim LastRow As Integer LastRow = Worksheets("Data").Range("B1").End(xlDown).Row Worksheets("Data").Range("A1").Formula = DataIdentifier Worksheets("Data").Range("A1").Copy Worksheets("LOOP").Range(Cells(1, 1), Cells(LastRow, 1)).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Worksheets("Data").Range("A1").ClearContent ------------------- This gives me an error at the paste-special line and I cannot figur out why. Note that what appears in the cells in column A need not necessarily b a formula as long as it has the right value. ie the subroutine coul assign it a value rather than a formula. I've tried doing this mysel but it didnt yield any speed increase. Question 1) What might be causing the error in the above code? Question 2) Am I barking up the wrong tree thinking the copy/past might be quicker than the first bit of code? Question 3) Does anyone have any suggestions for other ways to optimiz this process? Thanks -- ob3ron0 ----------------------------------------------------------------------- ob3ron02's Profile: http://www.excelforum.com/member.php...fo&userid=1545 View this thread: http://www.excelforum.com/showthread.php?threadid=27277 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to enter many formulas
hi, You can use formulas fill down. S.Sures -- s_u_res ----------------------------------------------------------------------- s_u_resh's Profile: http://www.excelforum.com/member.php...fo&userid=1567 View this thread: http://www.excelforum.com/showthread.php?threadid=27277 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to enter many formulas
Grüezi ob3ron02
ob3ron02 schrieb am 27.10.2004 I've got a database query filling column B-F of a worksheet (no blanks) with a variable number of rows (but usually many rows). I'm trying to determine the quickest method to fill column A with a formula that depends on the row's column B value. -------------------- Dim LastRow As Integer LastRow = Worksheets("Data").Range("B1").End(xlDown).Row Do While counter < LastRow Worksheets("Data").Range("A2").Offset(counter, 0).Formula = DataIdentifier Counter = Counter + 1 Loop -------------------- I don't know what 'DataIdentifier' is; did you define this value or formula earlier in your code? Question 3) Does anyone have any suggestions for other ways to optimize this process? You might try this one: Dim LastRow As Long 'if more than 32767 rows LastRow = Worksheets("Data").Range("B1").End(xlDown).Row Worksheets("Data").Range("A2:A"&LastRow).Formula = DataIdentifier -- Regards Thomas Ramel - MVP for Microsoft-Excel - [Win XP Pro SP-1 / xl2000 SP-3] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fastest Way to Filter/Delete | Excel Worksheet Functions | |||
Fastest way for comparing columns? | Excel Programming | |||
Find fastest way to do lookups | Excel Programming | |||
fastest hardware for excel | Excel Programming | |||
Fastest way to do this? | Excel Programming |