Countif result in variable

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?

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Any hints?

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...

Please Login or Register  to view this content.

Lovely !!
The first code works perfectly !!

Thanks for the help, very much appreciated