CODEWITHSUNDEEP
vbaexcel
Märten
Märten

Reputation: 41

Using a relative reference (offset) in a COUNTIF function in Excel VBA

I'm having problems using a relative reference (offset) in a COUNTIF function in Excel VBA:

buildRef.Offset(i, 2) = WorksheetFunction.CountIfs _
            (range, ">=" & buildRef.Offset(i, 1))

This always results in 0 but I know that it's not the correct answer. However,

WorksheetFunction.CountIfs (range, buildRef.Offset(i, 1)) does give the correct answer.

Can anyone tell me why the COUNTIFS function doesn't work when the criteria is "greater than" some relative reference?

Upvotes: 0

Views: 1163

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149315

There is nothing wrong with the code. You are getting 0 because of your i. The .Offset(i, 1) may not be referring to the cell you think it is referring to. I would recommend stepping through the code to check the value of i.

Here is an example to show it works. I have hard coded the value of i to demonstrate how it works.

Sub Sample()
    Dim rng As Range, buildRef As Range

    Set rng = [A1:A10]
    Set buildRef = [D1]

    buildRef.Offset(1, 2) = WorksheetFunction.CountIfs(rng, ">=" & buildRef.Offset(1, 1))
End Sub

enter image description here

Upvotes: 1

Related Questions