You're looking at a FOR loop and The SELECT CASE command, i think:
You could turn this into a function that accepts an input Range object to make things a little more flexible
If you can live with values in the 2nd row, you can avoid VBA entirely (which is usually good). By having a nested if statement below each cell (or a lookup table if things get complicated)
A2 could contain "=IF(OR(A1="No",A1="Poor"),5,IF(OR(A1="Yes",A1="Good"),1,IF(A1="Fair",3,0)))", which could be replicated across to I2 and this range summed.
Cheers
Mat
Bookmarks