Reference VBA object within worksheet function "countifs"

[pwells]

Hey guys, so I'm pretty new to VBA and I've been using it at my work a lot lately and I keep having a problem that my code isn't referencing the "=tier" in the countifs. Anyone know how to fix this?

Sub tiers()

Dim tier As Integer

For Each mycell In Sheets("New File").Range("c8:c27")
    tier = mycell.Row - 7
    mycell.Value = Application.WorksheetFunction.CountIfs(Sheets("Orig File").Range("a10:a99999"), ">0", _
    Sheets("Orig File").Range("u10:u99999"), "=tier")
Next mycell

End Sub

Any help is very much appreciated

[romperstomper]

Tier is a variable but by enclosing it in quotes you are treating it as literal text and the function tries to match the actual word "tier". You need to concatenate the value onto the criteria string:

Sub tiers()

Dim tier As Integer

For Each mycell In Sheets("New File").Range("c8:c27")
    tier = mycell.Row - 7
    mycell.Value = Application.WorksheetFunction.CountIfs(Sheets("Orig File").Range("a10:a99999"), ">0", _
    Sheets("Orig File").Range("u10:u99999"), "=" & tier)
Next mycell

End Sub

[pwells]

Woah, that worked perfectly! Thank you so much for the help

[nathansav]

i think you need to put some "" round tier. I'll take a look.