VBA: Help with countifs / Match

Hi,

Summary: Need to find out Position (especially column ) of a certain value ( input will be through code or in cell) and pass it as a first parameter to Countifs function

i have this ( headers)

A1:City1 B1:City2 C1:City4 D1:City6 E1:City5.....and appropriate values under each header.

..and the user input is in A2:City4 ( example)

i would need to find out the position of City ( primarily which column - here it would be column 3 or C) and then use this column as input to countifs to count the value.

sub test
value1=range("A2").value
'HLUP is defined name for A1:E1
Ymatch=Evaluate("=match(""" & value1 & """,HLUP,0)")
y=evaluate("=countifs(column(Ymatch),"=10")
msgbox y

The parameter in Bold should be substitued suitably which should access the corresponding Column

Something along these lines, perhaps:

Sub Test()

Dim vFindValue As Variant
Dim rngMatch As Range

vFindValue=Range("A2").Value

Set rngMatch=Range("HLUP").Find(vFindValue, Lookin:=xlValues, LookAt:=xlWhole, MatchCase:=False)

If rngMatch Is Nothing Then
  MsgBox "Value not found"
Else
  Msgbox WorksheetFunction.CountIf(rngMatch.EntireColumn,10)
End If

End Sub

Edited to add: And you do know that you could do this with a single formula and not bother with macros at all, don't you?