VBA: Help with countifs / Match

**vidyuthrajesh** · 02-29-2012, 12:29 PM

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.

Please Login or Register  to view this content.

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

**Andrew-R** · 02-29-2012, 12:38 PM

Something along these lines, perhaps:

Please Login or Register  to view this content.

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?

**vidyuthrajesh** · 02-29-2012, 01:32 PM

Thanx..it is resolved ( to answer ur question : i have 15 such columns and hence i am using vba code). Also just curious to know what is the correct usage of Evaluate as the code below does not seem to work and gives me #NAME

Evaluate("=countifs(rngmatch.entirecolumn,""" & vfindvalue1 & """ )") but the following works

WorksheetFunction.CountIfs(rngMatch.EntireColumn, "" & vfindvalue1 & "")

Note: I substitued the search criteria as Text instead of number... i tried ( in evaluate) 1/2/3 double quotes but could get only #NAME

**Andrew-R** · 02-29-2012, 01:42 PM

If you wanted to go with Evaluate then something like this...

Please Login or Register  to view this content.

Chr(34) is just equal to ASCII character 34, which is the " character, and rngmatch.entirecolumn.address will return the sheet address of the entire column, for example $A:$A.