Countif result in variable

[Vswildcat]

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

[AlphaFrog]

Try something like this.

   Dim x As Long
   Dim y As Long
   Dim i As Long
      
    x = Application.WorksheetFunction.CountIf(Sheets("Milestones and duration").Range("F:F"), "yes")
    y = Application.WorksheetFunction.CountA(Sheets("Partners").Range("A:A"))

Your copy\paste loop isn't quite right, but I don't follow what you want done.

[Vswildcat]

Hi,

Waou that was fast !! Thanks, worked like a charm.

Yeah, the copy/past thing wasn't quite right, I changed a little but there is still something I don't get:

I want the 7 cells (from Q2 to Q8) to be copied and pasted and repeat this x time (so 7 in my case).
So that's what I say:

Select the 7 cells
Copy them
move the active cell at the botom of the 7 cells
past
and do it again 7 times

with the below macro, it only does it 5 times

    
    x = Application.WorksheetFunction.CountIf(Sheets("Milestones and duration").Range("F:F"), "yes")
    y = Application.WorksheetFunction.CountA(Sheets("Partners").Range("A:A"))
    
    Range("Q2:Q8").Select
    For i = 0 To x
    Selection.Copy
    ActiveCell.Offset(x, 0).Select
    ActiveSheet.Paste
    i = i + 1
    Next

Any hints?

[AlphaFrog]

Select the 7 cells (from Q2 to Q8)
Copy them
move the active cell at the bottom of the 7 cells
past
and do it again 7 times

This is still confusing.

Do you want to paste to Q9:Q15 then Q16:Q22 then Q23:Q29...etc. x times?

[Vswildcat]

Hi,

Yes, you got it.
I tried to exclude the "copy" piece from the loop, and only have the "past and activecell.offset", but know it only do it 3 times instead of 7...

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

[Norie]

Perhaps.

Set rngSrc = Range("Q2:Q8")

For I = 0 To x
    rngSrc.Copy rngSrc.Offset(I*7)
Next I

Or perhaps.

With Range("Q2:Q8")
    .Copy .Resize(7*(x+1))
End With

[Vswildcat]

Lovely !!
The first code works perfectly !!

Thanks for the help, very much appreciated