Countif giving me 0

[tt.viquel]

I have changed this countif formula into a macro: but it is giving me 0 instead of 10 does anyone know why??

=COUNTIF(KTPT80T!G:G,"Y")

Sub test()
Dim x As Long
x = Range("G" & Rows.Count).End(xlUp).Row
If x < 100 Then x = 100
Range("Z2") = Application.WorksheetFunction.CountIf(Range("G5:G" & x), "Y")
End Sub

 

[Dartagnan]

Here is the code that is working for me.

Sub test()

Dim x As Long
x = Range("L" & Rows.Count).End(xlUp).Row
If x < 100 Then x = 100
Range("Z2") = Application.WorksheetFunction.CountIf(Range("L1:L" & x), "Y")

End Sub

 

[tt.viquel]

so you are definately getting the right value other than 0?? hmm let me double check again

 

[Dartagnan]

Definitely the right number. I have 100 Y's in column L starting at L1. The code returns 100 to Z2.

 

[tt.viquel]

thank you for your help, but i think it could be my excel then because it's giving me the error "excel has run out of resources can't process all formulas". So i know it is working I will use my other laptop.


Thank you again

quick question how would i change a counta statement e.g.

=COUNTA(KTPTZIT!$H:$H)-2

 

[Jonmo1]

Here's a thought...

Where are you putting this code?

It's possible it is not referring to the correct sheet.
Try adding the specific sheet name...

Sub test()
 
Dim x As Long
With Sheets("Sheetname with Ys in Column L") 'Adjust sheet name as needed<ADJUST font Needed.< as SheetName>
    x = .Range("L" & Rows.Count).End(xlUp).Row
    If x < 100 Then x = 100
    .Range("Z2") = Application.WorksheetFunction.CountIf(.Range("L1:L" & x), "Y")
End With
End Sub

Hope that helps.

 

[tt.viquel]

I have tried to reference the sheetname and it is still giving me 0.
I am starting to think it is my excel now because the code is correct.

 

[tt.viquel]

So i have discovered what the problem is I wasnt specifying the right sheet to put the results. And I have amended that but now it is not returning anything:

[COLOR=black][FONT=Tahoma]Sub test()
Dim x As Long
x = Range("G" & Rows.count).End(xlUp).Row
With Sheets("KTPT80T")
If x > 100 Then x = 100
Sheets("Index").Range("Z4") = Application.WorksheetFunction.CountIf(Range("G5:G" & x), "Y")
End With
End Sub<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]

 

[tt.viquel]

nevermind my last response it now works I have fixed it. Now i wanted to find out how do i add other countif to the same sub, (6 in total) with different criterias as well e.g.

=COUNTIF(TABFR73!J:J,"Y")

Or it would be wiser to do subs for the each countif??

 

[Dartagnan]

Thought I would share this. Does pretty much the same thing, but on multiple worksheets.

Sub newtest()

Dim x As Long
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Select
x = ws.Range("L" & Rows.Count).End(xlUp).Row
If x > 100 Then x = 100
ws.Range("Z4") = Application.WorksheetFunction.CountIf(Range("L1:L" & x), "Y")
Next ws

End Sub

 

[Dartagnan]

"quick question how would i change a counta statement e.g."

Change to?