VBA: Help with countifs / Match

[vidyuthrajesh]

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