Countif and range

[Link Strife]

Ok, my situation is that I need to count the non blank cells in a range. No matter how much I look at examples or tinker with my code it just does not seem to accpet it.

Say I have entries going every 7 spots starting at cell A6, that would mean by the time it reaches A75 there is 10 entries. Every time I attmept to count I get the Cells total, which is a number in the 70+, I don't want that. So how do I get just the cells that have something in them?

Dim c As Integer
c = 0
Range("a6:a75").Select
c = CountIf(Range("a6:a75"), "<>")

Range("a2").Value = c

Thanks Plenty

Link S

[anilsolipuram]

You are almost correct, you enter this formula at the cell where you want the result as
=COUNTIF(A6:A75, "<>")

or if you want to use the vba code for this use

Application.Evaluate("=CountIf(a6:a75,""<>"" )")

[Link Strife]

Alright, what if I wanted to combine the countif statement and the range statement?

Please excuse my Frankencode but could this be done?
c = CountIf(Range("a6:a" & (i + 5)), "<>")
or
c = Application.Evaluate("=CountIf(Range("a6:a" & (i + 5)),""<>"" )")
Thanks again Anil

Link S

[Link Strife]

Still working on it...
If I use the top one
it gives me the error
Compile Error: Sub or Function Not Defined

[Link Strife]

I am at a loss, so far no matter what I do to combine the range and the Countif functions, it always gives me an error. I could use part of Anil's code, but I need to count a specific area that is recalculated every time the macro is run.

Does anybody know if it can be done?

Link S

[Link Strife]

I have run into a problem with my code up to this point. I need to take the Cells in "Summary" and get them to automatically update themselves using my macro. First I had to find out how many projects were on the workbook, then go to the summary section. I have managed after much toil and trouble to finally get the number of projects and go to the summary section.

My new problem is that I don't know how to code the formulas effectively, or in a way that the compiler will accept it.

count = 84 ' this is the number of rows that I have counted, which is equal to the number of projects when this number is divided by the rows per project. The rows per project in this case is 7.

Now if i continue as is I can set up the formula this far.

Dim g As Integer
For g = count / 7 To 0
ActiveCell.FormulaR1C1 = "=R[-78]C+R[-71]C+R[-64]C+R[-57]C+R[-50]C+R[-43]C+R[-36]C+R[-29]C+R[-22]C+R[-15]C+R[-8]C"
Next g

As you can see the ActiveCell.Formula works for a specific number of cells, I can't use that because the number of projects can get bigger then this range.

What I am hoping to set up for the active cell formula is this
ActiveCell.FormulaR1C1 = "=R[-"count *7"]C+"
then somehow add to the end of this formula the middle parts after count has decrimented correctly.
ActiveCell.FormulaR1C1 = "R[-count*7"]C+"
and finally on the end of it all when count is = to either 0 or 1
ActiveCell.FormulaR1C1 = "=R[-"count *7"]C"

I know I might need to use a switch case statement somehow, but as for actually writing it and adding a formula onto itself, I am at a total loss.

Any help will work, I have included the workbook for peoples reference.
Please and Thank you for your time

Link S

[Link Strife]

Does anybody know how to do this?
or am I attempting to do the impossible?

Any help is appreciated
and thanks to those who tried

Link Strife