Hi,

Long time I haven't done macros, seems I'm a bit rusty.
I'm sure this is a simple fix, but just can't find it, so hope you can help.

I would like the result of my countif to be used as a variable later on in the macro see below.

x is the number of time I have "yes" in my column F in my worksheet "milestones and duration".
I can tell you the result is 7.

I want then use that result, 7, in the loop, so instead of saying for i = 1 to 7, I'd like to say for i = 1 to x

Excel returns Type mismatch error
It also returns error when I try to use x in the activecell.offset

I will then have a "bigger" loop to repeat the "small" loop y times, but that's the next step, I'm not there yet, just trying to fix the first issue

Is it because I didn't declare x properly?

      
   Dim x
   Dim y
   Dim i
      
    x = "=COUNTIF('Milestones and duration'!F:F,""yes"")"

    y = "=COUNTA(Partners!A:A)"
    
    Range("Q2:Q8").Select
    For i = 0 To x
    Selection.Copy
    ActiveCell.Offset(x, 0).Select
    ActiveSheet.Paste
    ActiveCell.Offset(x, 0).Select
    i = i + 1
    Next