Run-time error from my code

[Matt]

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

 

[nath]

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

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

 

[Matt]

I get the same problem. My problem is with the .formula line of code. Thanks for trying.
Matt

 

[Michael Malinsky]

You need to modify your formula line to the following:

Worksheets("Sheet1").Range("C31,E31,G31,I31,K31,M31,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

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

 

[Matt]

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").FormulaR1C1 = "=IF(RC[1] <> R[-21]C[1],""ERROR"","""")"

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.

You need to modify your formula line to the following:

Worksheets("Sheet1").Range("C31,E31,G31,I31,K31,M31,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

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

Else
Worksheets("TIME AND LEAVE").Range("C31,E31,G31,I31,K31,M31,O31").ClearContents
End If
End Sub

 

[Dave Peterson]

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").FormulaR1C1 _
= "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).

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").FormulaR1C1 = "=IF(RC[1] <> R[-21]C[1],""ERROR"","""")"

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.

You need to modify your formula line to the following:

Worksheets("Sheet1").Range("C31,E31,G31,I31,K31,M31,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

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