the "M9" reference is because I needed the column to stop populating when the
total in M9 reached zero.

I am home now but will try your solution tomorrow Tom.

I apreciate your time!

"Tom Ogilvy" wrote:

Instead of you loop Try this


With Range("M14").Resize(lastrow-13,1)
.Formula = Range("A1").Formula
On Error Resume Next
.SpecialCells(xlFormulas,xlErrors).ClearContents
On Error goto 0
End With

Not sure how M9 figures into it.
--
Regards,
Tom Ogilvy

"Jane" wrote in message
...
Tom, here is my loop which refers to A1 where I put that formula-the
formula
form which to paste
Lastrow = Range("E65536").End(xlUp).Row

'----------------
Range("m14", "m813").FormulaR1C1 = "0"

For I = 14 To Lastrow
Range("A1").Copy
Range("m" & I).Select
ActiveSheet.PasteSpecial
If Range("m" & I).Text = "#N/A" Then
Range("M" & I).ClearContents
End If
If Range("m9").Value < 0 Then
Range("m" & I).ClearContents
Exit For
End If
Next I
This is a large spreadsheet so the loopign that's a more than acceptable
amount of time.

When you say, at the bottom of your response, to adjust ranges and paste,
I;m not sure I understand what you mean I would rather have the formula
in
the cells where they belong.

I hope I am not confusing th eissue further.
j

"Tom Ogilvy" wrote:

s =

"=AND($M$2=1,$I14=1,$M$3<""n"")*BK14+AND($N$2=2,$ I14=2,$N$3<""n"")*BK14+AN

D($O$2=3,$I14=3,$O$3<""n"")*BK14+AND($P$2=4,$I14= 4,$P$3<""n"")*BK14+AND($Q

$2=5,$I14=5,$Q$3<""n"")*BK14+AND($R$2=6,$I14=6,$R $3<""n"")*BK14+AND($S$2=7

,$I14=7,$S$3<""n"")*BK14+AND($T$2=8,$I14=8,$T$3< ""n"")*BK14+AND($U$2=9,$I1
4=9,$U$3<""n"")*BK14+AND($V$2=10,$I14=10,$V$3<"" n"")*BK14"
? len(s)
342
ActiveCell.Formula = s

worked fine for me, so it isn't too long.

assume this formula is in cell A1

then

ActiveCell.formula = Range("A1").formula

shouldn't take very long. if you want the ranges to adjust, then use
copy
and paste.

--
Regards,
Tom Ogilvy


"Jane" wrote in message
...
I have an existing formula in a cell:


'=AND($M$2=1,$I14=1,$M$3<"n")*BK14+AND($N$2=2,$I1 4=2,$N$3<"n")*BK14+AND($O

$2=3,$I14=3,$O$3<"n")*BK14+AND($P$2=4,$I14=4,$P$3 <"n")*BK14+AND($Q$2=5,$I1

4=5,$Q$3<"n")*BK14+AND($R$2=6,$I14=6,$R$3<"n")*B K14+AND($S$2=7,$I14=7,$S$3

<"n")*BK14+AND($T$2=8,$I14=8,$T$3<"n")*BK14+AND( $U$2=9,$I14=9,$U$3<"n")*B
K14+AND($V$2=10,$I14=10,$V$3<"n")*BK14
So I would not have to type it into my macro, I highlighted this
cell,
put
my cursor to the end of the formula, hit Record Macro, and then Enter.
Then
proceeded to edit my marco.

Yesterday this worked, today I received an "Unable to Record" error.
I
was
told that this occurred because My formula was too long. Is that
true?
How
can I avoid this error yet not have to write this formula manually
into my
marcro.

ps... my short term solution was to copy and paste this formula into a
cell
and have the macro paste it into my range. The problem with this is
that
it
takes to long to run my macro.

thank you in advance for your help.... jane