|
"Unable to Record" error
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")*B K14+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 |
"Unable to Record" error
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 |
"Unable to Record" error
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 |
"Unable to Record" error
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 |
"Unable to Record" error
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 |
| All times are GMT +1. The time now is 09:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com