View Full Version : [SOLVED:] Code for a changable range in COUNTIF function
martram
11-20-2017, 05:12 AM
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
11-20-2017, 07:21 AM
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
11-20-2017, 08:16 AM
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
11-20-2017, 08:51 AM
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
11-20-2017, 10:06 AM
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
11-20-2017, 11:04 AM
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
11-21-2017, 01:18 AM
Yes, this works! Many thanks!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.