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

-----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

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

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