Categorizing Column Values Using Multiple Criteria

[BrotherDude]

Hello all,

I am working with an aging report where data is formatted in column. In the last column I would like to assign a value based on criteria from the adjacent columns (ex: If balance > $10,000 then value = "Hold").

My idea was that I would asign variable to the column, start a for next loop, and insert the criteria in an if then else loop. Is this the best way to go about it, should I be using case select, and how do best assign the variable to column so that I can loop down the entire report.

Here's an example of what I'm working with

[TABLE="width: 854"]
<colgroup><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD] Balance [/TD]
[TD] Current [/TD]
[TD]90[/TD]
[TD]120[/TD]
[TD]150[/TD]
[TD]180+[/TD]
[TD] Pymt to Date [/TD]
[TD]Last Pymt [/TD]
[TD] Last Pymt [/TD]
[TD] Status [/TD]
[/TR]
[TR]
[TD] 230,426.63[/TD]
[TD] 157,857.42[/TD]
[TD] 6,300.00[/TD]
[TD] 4,546.14[/TD]
[TD] 16,600.00[/TD]
[TD] 45,123.07[/TD]
[TD] 87,341.48[/TD]
[TD]7/17/2014[/TD]
[TD] 13,150.00[/TD]
[TD]"Hold"[/TD]
[/TR]
[TR]
[TD] 154,665.00[/TD]
[TD] 154,665.00[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 35,000.00[/TD]
[TD]7/14/2014[/TD]
[TD] 25,000.00[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD] 143,672.80[/TD]
[TD] 136,255.95[/TD]
[TD] 8,250.00[/TD]
[TD] 5,096.85[/TD]
[TD] (4,830.00)[/TD]
[TD] (1,100.00)[/TD]
[TD] 26,145.23[/TD]
[TD]7/25/2014[/TD]
[TD] 2,140.00[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]


any suggestions are greatly appreciated,

- Jeff

 

[irakrakow]

Do the following:

1) Create a range name for the numbers in the Balance column. Call it Balance.
2) Highlight the cells in the Status column.
3) Press F2, which lets you enter a formula in the first cell of the Status column.
4) Type the following formula (DON'T PRESS ENTER).

=IF(Balance>100000,"HOLD","?")

5) Press Ctrl+Shift+Enter. This creates an array formula which does the IF test for each number in the balance column. In the formula bar you will
see braces surrounding the formula. Excel is telling you that you created an array formula. There is no need to get into VBA to do this.

 

[BrotherDude]

This would need to be a VBA solution as the number of criteria is simply too high to fulfill with a formula, the balance example would be the first of up to 15 different criteria I want to use to define a status. I guess my biggest questions are, how should I define the cells in the adjacent columns as the vba loops down the report (should I just use cell column references or can I name column and/or set variables for more for flexibility in the code), and second, should I we using elseif or select case for the criteria?