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]

Strange your code is working perfect for me. Are all column/cell references correct?

 

[tt.viquel]

Hi,

really it worked for you, it is working but it is suppose to return the value of 10 as there are 10 Y's but it is giving me the result of 0. I am definately sure i referenced the cells correctly

Thanks

 

[Dartagnan]

Just a thought. I noticed you are starting at G5. Is that the correct starting cell? I did get 0 when my column/cell references were off. For example, I deleted some columns, but didn't update the code. Once I changed the code to the new columns the code worked again.

 

[tt.viquel]

]G5 is the column where the Y's start from, yes it's the correct cell. Do you think it could be this part :

If x < 100 Then x = 100

 

[Dartagnan]

How many rows are there?

 

[tt.viquel]

in column G it goes up to G14 that is the last Y, but I want it to be dynamic as there will be continual amendments to it...deleting and adding more data

 

[Werrf]

What value is X receiving?

 

[tt.viquel]

values from the range G being Y, or maybe i wrote it wrong i think this is the source of my problem

 

[Dartagnan]

Don't see that as a problem. Whether I make 100 or over 100 rows with Y I get the right count.