"Unable to Record" error

[Jane]

I have an existing formula in a cell:
'=AND($M$2=1,$I14=1,$M$3<>"n")*BK14+AND($N$2=2,$I14=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,$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,$I14=9,$U$3<>"n")*BK14+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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> I have an existing formula in a cell:
>
'=AND($M$2=1,$I14=1,$M$3<>"n")*BK14+AND($N$2=2,$I14=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")*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,$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

[Jane]

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" <[email protected]> wrote in message
> news:[email protected]...
> > I have an existing formula in a cell:
> >
> '=AND($M$2=1,$I14=1,$M$3<>"n")*BK14+AND($N$2=2,$I14=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")*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,$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
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have an existing formula in a cell:
> > >
> >
'=AND($M$2=1,$I14=1,$M$3<>"n")*BK14+AND($N$2=2,$I14=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")*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,$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
> >
> >
> >

[Jane]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have an existing formula in a cell:
> > > >
> > >
> '=AND($M$2=1,$I14=1,$M$3<>"n")*BK14+AND($N$2=2,$I14=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")*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,$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
> > >
> > >
> > >
>
>
>