You're looking at a FOR loop and The SELECT CASE command, i think:
sub AddValues()
dim val as long 'this creates an empty long integer variable for the result
For i = 1 To 9 ' i.e. from columns A to I
Select Case Cells(1, i)
Case Is = "No"
Val = Val + 5
Case Is = "Yes"
Val = Val + 1
... repeat for all other cases
End Select
Next i
cells(10,1)=val
end sub
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