Recursive function that updates columns

[lundy]

Hey guys,

is there a way to make the following equation

"=COUNTIF(B:B,"">"" & " & Selection.Value & ")+ COUNTIF(D:D,"">"" & " & Selection.Value & ")"

into a single COUNTIF() that moves the column over automatically.

OR

since I can make a column into a number like Selection.column will return a number for the column you are in, can I then go in reverse and make the number 4 into column D somehow??

[Norie]

You can't make that into one formula, you are counting 2 different columns.

Not sure what your 2nd part is asking.

Selection.Column will give you the column number if the Selection is a range.

You could get the column letter from that but I'm not sure why you would want it.

[lundy]

well, since selection.column would equal 2 if you were in column B I was just wondering if I could somehow make the number two indicate column B so that I could get COUNTIF() to move columns without physically putting in the next column.

Just trying to make a recursive COUNTIF() that would move over a selected number of columns at a time so I could compare Selection.Value to multiple columns as long as they have values in them

[Norie]

Sorry I'm really not following this.

Selection is the value you are using for the criteria, it has nothing to do with which columns are counted.

[lundy]

ok forget the other stuff for a second,

in COUNTIF() i have a column and a value i need. lets assume the column to start with is column B:B and the value is A1

I can iterate through the value using selection.offset(1,0) to go down the column, till i get to the end of data in column A. At this point I want to compare column C:C to the values in column A so the equation goes from

COUNTIF(B:B,"">"" & " & selection.value &") to COUNTIF(C:C,"">"" & " & selection.value &") can I cycle through columns like I cycle through the values so that I dont have to type every column to compare too?

If i still confuse you, I am sorry for my inability to explain this