View Full Version : Solved: How do I code this

07-07-2006, 07:03 AM
In this spreadsheet I have a formula in D2 as

" If(G2<0.01,"",If(G2>F2,"Y","N"))"

If I code it as the below when added to the Sheet 1 code it asks me to create a Macro. But in the macro code it says "Compile Error "Expected end of Statement" at the "0.01" value.

.Range.FormulaR1C1 = "=If(R[2]C[8]<"0.01","",If(R[2]C[8]>R[2]C[7],"Y","N")) it displays a Compile error when you try to run the code from the Macro ContractStatus.

The object here is have the value in cell D2 ( actually all of the Range D2:D156) reflect if the value in G2 is "0" then show nothing, else if the value in G2 is greater than F2 then show the value "Y" else "N".

By typing a date in column T and a value "Cleared" in Column U, the code (Kindly initially supplied by XLGibbs) copies and paste to the Sheet "Bucket History" and then resets the values in the Sheet 1 (Except it doesn't rest the Column D values)

07-07-2006, 07:05 AM
Hmmm... lost the attachment I see.

07-07-2006, 07:11 AM
Okay really found it this time...

07-07-2006, 07:12 AM
Try this.

Range("D2:D156")= "=If(G2<0.01,"""",If(G2>F2,""Y"",""N""))"

07-07-2006, 07:20 AM
Sorry, still didn't reset the formula for the cell in the row just copied.

07-07-2006, 07:22 AM
Sorry but I have no idea what you mean.:)

You didn't post any code that copied a row.

Unless it's in the attachment, which I'm about to download.

07-07-2006, 07:36 AM
Yep. Its in the attachment

07-07-2006, 08:12 AM
So how did you try the code I posted and how did it not work?

07-07-2006, 11:49 AM
If that does not work, you may try:

Range("D2:D156").Formula = "=If(G2<0.01,"""",If(G2>F2,""Y"",""N""))"

07-07-2006, 02:34 PM
The problem may lie inside the macro I believe.

07-08-2006, 02:37 AM
Range("D2:D156").FormulaR1C1 = "=IF(RC[3]<0.01,"""",IF(RC[3]>RC[2],""Y"",""N""))"