Solved: How do I code this

[Aussiebear]

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

[Aussiebear]

Hmmm... lost the attachment I see.

[Aussiebear]

Okay really found it this time...

[Norie]

Try this.
[vba]
Range("D2156")= "=If(G2<0.01,"""",If(G2>F2,""Y"",""N""))"
[/vba]

[Aussiebear]

Sorry, still didn't reset the formula for the cell in the row just copied.

[Norie]

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.

[Aussiebear]

Yep. Its in the attachment

[Norie]

So how did you try the code I posted and how did it not work?

[jungix]

If that does not work, you may try:

[VBA]

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

[/VBA]

[Aussiebear]

The problem may lie inside the macro I believe.

[mdmackillop]

[VBA]Range("D2156").FormulaR1C1 = "=IF(RC[3]<0.01,"""",IF(RC[3]>RC[2],""Y"",""N""))"
[/VBA]