Fastest way to enter many formulas

[ob3ron02]

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

 

[s_u_resh]

hi,

You can use formulas fill down.

S.Sures

 

[Thomas Ramel]

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]