| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error from my code
I have this code that is placing a formula in a range of cells if a control checkbox(cbExempt) is false. Then if cbExempt is true, it is clearing the contents of that same range of cells. I am getting a runtime error(1004):Application-defined or object-defined error when I run this code. The problem seems to be the line where it places the formula in the range of cells. At least that is the line that the debugger is highlighting. Any suggestions would be great. Thanks. Matt
Public Sub Formula() If Worksheets("TIME AND LEAVE").cbExempt.Value = False Then Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Formu la = "=IF(RC[1] < R[-21]C[1],'ERROR','')" Else Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Clear Contents End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error from my code
-----Original Message----- I have this code that is placing a formula in a range of cells if a control checkbox(cbExempt) is false. Then if cbExempt is true, it is clearing the contents of that same range of cells. I am getting a runtime error (1004):Application-defined or object-defined error when I run this code. The problem seems to be the line where it places the formula in the range of cells. At least that is the line that the debugger is highlighting. Any suggestions would be great. Thanks. Matt Public Sub Formula() If Worksheets("TIME AND LEAVE").cbExempt.Value = False Then Worksheets("TIME AND LEAVE").Range ("C31,E31,G31,I31,K31,M31,O31").Formula = "=IF(RC[1] < R[- 21]C[1],'ERROR','')" Else Worksheets("TIME AND LEAVE").Range ("C31,E31,G31,I31,K31,M31,O31").ClearContents End If End Sub . Try using .select then selection.clearcontents then range("a1").select to get back to a "home" position nath |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error from my code
I get the same problem. My problem is with the .formula line of code. Thanks for trying.
Matt "Matt" wrote: I have this code that is placing a formula in a range of cells if a control checkbox(cbExempt) is false. Then if cbExempt is true, it is clearing the contents of that same range of cells. I am getting a runtime error(1004):Application-defined or object-defined error when I run this code. The problem seems to be the line where it places the formula in the range of cells. At least that is the line that the debugger is highlighting. Any suggestions would be great. Thanks. Matt Public Sub Formula() If Worksheets("TIME AND LEAVE").cbExempt.Value = False Then Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Formu la = "=IF(RC[1] < R[-21]C[1],'ERROR','')" Else Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Clear Contents End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error from my code
You need to modify your formula line to the following:
Worksheets("Sheet1").Range("C31,E31,G31,I31,K31,M3 1,O31").FormulaR1C1 = "=IF(RC[1] < R[-21]C[1],""ERROR"","""")" Your error was in how you were defining your TRUE and FALSE items in the IF statement. You were using single ticks (') where Excel requires double quotes around a string in this situation. HTH -- Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bother me." -- AA Milne, Winnie the Pooh "Matt" wrote in message ... I have this code that is placing a formula in a range of cells if a control checkbox(cbExempt) is false. Then if cbExempt is true, it is clearing the contents of that same range of cells. I am getting a runtime error(1004):Application-defined or object-defined error when I run this code. The problem seems to be the line where it places the formula in the range of cells. At least that is the line that the debugger is highlighting. Any suggestions would be great. Thanks. Matt Public Sub Formula() If Worksheets("TIME AND LEAVE").cbExempt.Value = False Then Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Formu la = "=IF(RC[1] < R[-21]C[1],'ERROR','')" Else Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Clear Contents End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error from my code
I tried what Michael mentioned to no avail. I'm still getting the application-defined or object-defined error. Can anyone see what is causing the problem with the following code. I'm at a loss of ideas of things to try. I have the following code under the Workbook_Open sub. Thanks. Matt
Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Formu laR1C1 = "=IF(RC[1] < R[-21]C[1],""ERROR"","""")" "Matt" wrote: I tried that and it won't accept double quotes. It tells me "End of statement expected". I assume you meant to only put in one set of double quotes, not two. I tried it both ways, with one set or two and still got my original error. Thanks. "Michael Malinsky" wrote: You need to modify your formula line to the following: Worksheets("Sheet1").Range("C31,E31,G31,I31,K31,M3 1,O31").FormulaR1C1 = "=IF(RC[1] < R[-21]C[1],""ERROR"","""")" Your error was in how you were defining your TRUE and FALSE items in the IF statement. You were using single ticks (') where Excel requires double quotes around a string in this situation. HTH -- Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bother me." -- AA Milne, Winnie the Pooh "Matt" wrote in message ... I have this code that is placing a formula in a range of cells if a control checkbox(cbExempt) is false. Then if cbExempt is true, it is clearing the contents of that same range of cells. I am getting a runtime error(1004):Application-defined or object-defined error when I run this code. The problem seems to be the line where it places the formula in the range of cells. At least that is the line that the debugger is highlighting. Any suggestions would be great. Thanks. Matt Public Sub Formula() If Worksheets("TIME AND LEAVE").cbExempt.Value = False Then Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Formu la = "=IF(RC[1] < R[-21]C[1],'ERROR','')" Else Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Clear Contents End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-time error from my code
This worked ok for me.
One way I use to test my formulas: First, tools|Options|General|check R1C1 reference style Then drop the leading equal sign from your macro's formula: Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Formu laR1C1 _ = "IF(RC[1] < R[-21]C[1],""ERROR"","""")" Then back to excel and put the equal sign back into the formula and see why excel is yelling. Don't forget to change the R1C1 reference style back to A1 (if you want). Matt wrote: I tried what Michael mentioned to no avail. I'm still getting the application-defined or object-defined error. Can anyone see what is causing the problem with the following code. I'm at a loss of ideas of things to try. I have the following code under the Workbook_Open sub. Thanks. Matt Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Formu laR1C1 = "=IF(RC[1] < R[-21]C[1],""ERROR"","""")" "Matt" wrote: I tried that and it won't accept double quotes. It tells me "End of statement expected". I assume you meant to only put in one set of double quotes, not two. I tried it both ways, with one set or two and still got my original error. Thanks. "Michael Malinsky" wrote: You need to modify your formula line to the following: Worksheets("Sheet1").Range("C31,E31,G31,I31,K31,M3 1,O31").FormulaR1C1 = "=IF(RC[1] < R[-21]C[1],""ERROR"","""")" Your error was in how you were defining your TRUE and FALSE items in the IF statement. You were using single ticks (') where Excel requires double quotes around a string in this situation. HTH -- Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bother me." -- AA Milne, Winnie the Pooh "Matt" wrote in message ... I have this code that is placing a formula in a range of cells if a control checkbox(cbExempt) is false. Then if cbExempt is true, it is clearing the contents of that same range of cells. I am getting a runtime error(1004):Application-defined or object-defined error when I run this code. The problem seems to be the line where it places the formula in the range of cells. At least that is the line that the debugger is highlighting. Any suggestions would be great. Thanks. Matt Public Sub Formula() If Worksheets("TIME AND LEAVE").cbExempt.Value = False Then Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Formu la = "=IF(RC[1] < R[-21]C[1],'ERROR','')" Else Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").Clear Contents End If End Sub -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
| Run time error 1004, General ODBC error | New Users to Excel | |||
| How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
| Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming | |||
| Code Run-time error '1004' | Excel Programming | |||
Linear Mode
