Code for a changable range in COUNTIF function

[martram]

Hi, I am new to VBA.

I use this code: Worksheets("Data").Range("t19").Value = [COUNTIF(Data!W40:W174,">0.5")]

It is working nicely but I would like to use it in a loop and change the range W40:W174 in each iteration (i): W40:W(i). For example, for i = 173 the range would be W40:W173.
How should I rewrite the code to have a changeable range?

I tried the following but it is not working:

Dim i As Integer
i = 174
Worksheets("Data").Range("t19").Value = [COUNTIF(Range(.Cells(40, 23), .Cells(i, 23)),">0.5")]


Thank you in advance for any help.

[Paul_Hossler]

Not tested, but I usually do something like this

Dim sAddr as string

sAddr = "Data!" & Range(.Cells(40, 23), .Cells(i, 23)).Address

Worksheets("Data").Range("t19").Value = Application.WorksheetFunction.CountIf (sAddr, ">0.5")

[martram]

Thank you, Paul, but it is not working.

It gives me this message: "Compile error: Invalid or unqualified reference" and highlights ".Cells".

Maybe there is an error in the Range(.Cells(40, 23), .Cells(i, 23)) part of the code which I cannot see?

[Paul_Hossler]

Without a sample workbook and the rest of the macro, it's still just guessing (you can attach one -- look at #2 in my signature)

I assume that since you used .Cells() in your sample that you were inside a With / End With

If not, then try

With Worksheets("Data")
sAddr = "Data!" & Range(.Cells(40, 23), .Cells(i, 23)).Address
End With

[martram]

I am sorry. I attach the workbook and macro now. It is this simple for now:

[CODE]
Sub Count()

Dim sAddr As String
Dim i As Integer
i = 174


With Worksheets("Data")
sAddr = "Data!" & Range(.Cells(40, 23), .Cells(i, 23)).Address
End With

Worksheets("Data").Range("t19").Value = Application.WorksheetFunction.CountIf(sAddr, ">0.5")

End Sub
[/CODE ]

Now it gives me: "Compile error: Type mismatch" nad highlights the second sAddr.

Thank you for help.

[Paul_Hossler]

Sorry - I guess I was in a hurry and forgot the Range( )

This returns 61

Option Explicit

Sub Count()
Dim sAddr As String
Dim i As Integer
i = 174
 
With Worksheets("Data")
    sAddr = "Data!" & Range(.Cells(40, 23), .Cells(i, 23)).Address
    .Range("t19").Value = Application.WorksheetFunction.CountIf(Range(sAddr), ">0.5")
End With
 
End Sub

[martram]

Yes, this works! Many thanks!