Compile Error

[Jamm_027]

Here is a portion of my code that inserts a formula for me.

With Range("A2", Cells(Cells.Rows.Count, 1).End(xlUp))
      .Offset(, 12).FormulaR1C1 = ("=COUNTIF(R1C1:R[-1]C1,R[-1]C[-12])>180")
End With

Works perfectly but now I want to make one small change. Instead of always being greater than "180". I need it to be greater than the value found on sheet2 in cell H2. I tried this but got a compile error....

With Range("A2", Cells(Cells.Rows.Count, 1).End(xlUp))
      .Offset(, 12).FormulaR1C1 = ("=COUNTIF(R1C1:R[-1]C1,R[-1]C[-12])>sheets("Sheet2").Range("H2").Value)
End With

thanks,

jamm

 

[Richard Schollar]

Hi Jamm

Try this:

With Range("A2", Cells(Cells.Rows.Count, 1).End(xlUp))
      .Offset(, 12).FormulaR1C1 = "=COUNTIF(R1C1:R[-1]C1,R[-1]C[-12])>" & sheets("Sheet2").Range("H2").Value
End With

 

[Jamm_027]

Got it...

thanks!