VBA: HOW TO loop countif

[montecarlo2012]

Good Morning, Happy friday. 5:45 am here in Orlando.
I am trying to use CountIf in a loop but I see my logic it is not working
this is what I tried in order to figure out the logic

so base on this I assume M would be a variable i, MX variable j and A variable k, and base on my start point and my end point I come out with this

Sub AK()
Dim i As Integer, j As Integer, k As Integer
For i = 3 To 95
For j = 12 To 54
For k = 1 To 95

Sheet2.Cells(3, 12).Formula = "=COUNTIF(SHEET1!I:J,sheet2!K)"
Next
Next
Next

End Sub

and of course do not work, I am very disappointed with myself.
this is what I get manually.

So, PLEASE
how can I resolve this,
First, thank you for reading this
second, would be nice to hear from you guys, and please tell me why my logic do not work.

 

[McGran]

This should do the trick.

Sub AK()
Dim colcount As Long, lastrow As Long, lastcol As Long

lastrow = Range("B1").End(xlDown).Row
lastcol = Range("A2").End(xlToRight).Column

For colcount = 2 To lastcol

    Cells(3, colcount).FormulaR1C1 = "=COUNTIF(Sheet1!R" & colcount + 1 & "C13:R" & colcount + 1 & "C362,RC1)"
    
Next colcount

Range(Cells(3, 2), Cells(3, lastcol)).Copy Range(Cells(4, 2), Cells(lastrow, lastcol))

End Sub

 

[montecarlo2012]

McGran, Thanks for your Code.
This code populate only the Row B3:MX3, do not fill nothing else, and at the end give me a debug message on:
Range(Cells(3, 2), Cells(3, lastcol)).Copy Range(Cells(4, 2), Cells(lastrow, lastcol))
Thanks.

 

[McGran]

Yes, the code populates the first row then the last bit of code (the bit that failed) copies it to the other rows.

What does the debug say?

Also paste in your new code so I can check it's a match.

Cheers

 

[montecarlo2012]

McGran, Thank you. I check your code in my job (32bits), now at home (64b), work perfect, so I can say, sorry false alarm. You did great job.
I am impress, I would like, IF it is possible for you, share some of your insides, mean your logic when you have this questions.
I saw in this forum some guys , they explain how they did it, of course this is up to you.
Thank you McGran.

 

[McGran]

I'm no expert but I'll share how I approached this.

I looked at your example formulae to find what changed each time and what did not. I noticed that the reference was always A3 on the first row, A4 on the second and so on. When you copy a formula down this happens automatically so, for this part, I only need to write the initial formula - Excel will correctly do the rest when I copy down.

For the range I saw that the columns are always fixed at M-MX and the row number for the first row increased by one for each column move, so with a simple counter I can always identify the correct column number by adding one to the previous column number (Column B is 2, C is 3 etc). When doing this I find it much easier to use the R1C1 reference method rather than the A1 method as this shows columns as numbers.

So the code firstly finds the last row and last column you are going to return data for.
Then we add in our counter (using a For Next loop) so we know which column to write the formula into. It starts at 2 because that is the first column that will have a formula. So colcount will start at 2 then each time the code passes Next colcount it increases by 1. Once colcount is higher than lastcol it will exit the loop.

The code within the loop has two parts. The left side identifies where the formula will be written and the right side is the formula you created, but in R1C1 style rather than A1. By including colcount in the formula the referenced row increases by one for each loop. So, in simple terms, we are saying, each time you move over a column add 1 to the row count that you look up.

After the loop simply copies the first row of formulae down to all rows.


I hope you find that helpful.

 

[montecarlo2012]

McGran, Thank you so much, You did great.
"Give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime"
Your Logic feed me more than the code.

 

[montecarlo2012]

I am trying to apply that logic now to a frequency function but this is my new thread, ok have a nice day