VBA copy formula instead of value

[Strooprover]

Hi,

I've got this code to copy the formula in F1 below for the amount of values in G1. But I think that in my last line I somehow give the order to copy the value of F1, instead of the formula of F1. At least, that's that happens in my worksheet. Can someone explain to me what I'm doing wrong?

  Range("F1").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet1!C[-5],Sheet2!RC[+2])"
lastrow = Worksheets("Sheet2").Range("H1").End(xlDown).Row
With Worksheets("Sheet2").Range("F1")
.AutoFill Destination:=Range("F1:F" & lastrow)
End With

 

[mrhstn]

Hi

Your code appears to put formulas when I tried it. You could simplify it though...

lastrow = Worksheets("Sheet2").Range("H1").End(xlDown).Row


Worksheets("Sheet2").Range("F1:F" & lastrow).FormulaR1C1 = "=COUNTIF(Sheet1!C[-5],Sheet2!RC[+2])"

 

[Strooprover]

Thanks for your help!

Can I ask you another question?

"I keep getting the Object variable or With block variable not set" error in the red line. Is it because I compare a string variable with a regular variable?

Thanks in advance!

Dim tabel As Stringtabel = Range("A1").Value


[COLOR=#ff0000]If ws2.Cells(x, 1) = tabel Then[/COLOR]
ws2.Cells(x, 1).Select