VBA Code If Cell Equals Text Then Assign Value and Sum

Hello,

I'm very new to VBA code writing and so I thought I'd get some assistance from this forum. I'm trying to write a VBA code wherein if certain cells contain specific text, it equates the text to a value and then adds them altogether into 1 cell.

Basically, I have a worksheet that looks like this

A1 = No
B1 = Yes
C1 = 0
D1 = Yes
E1 = Good
F1 = Fair
G1 = Good
H1 = 0
I1 = Poor

I need the VBA code in cell J1 to state that If text = No or Poor, then equate No and Poor to the value 5, if text = Fair, then equate value to 3, and if text = Yes or Good, value =1, then sum them all up. The cells with 0 in them need to be ignored. Therefore in Cell J1 under the example above, this should equate to a final value of 17 (A1 = 5, B1 =1, C1 (INGNORED), D1 = 1, E1 = 1, F1 = 3, G1 = 1, H1 (IGNORED), I1 = 5 for a total of 17)

If someone could please assist me in writing the VBA code for this, it would be greatly appreciated.

Thank you.

Try:

Please Login or Register  to view this content.

You're looking at a FOR loop and The SELECT CASE command, i think:

Please Login or Register  to view this content.

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