I wouldn't even bother using VBA as you were attempting. Just use a
VLOOKUP() function within EXCEL. For example, to hardcode the list,
you could do something like:

=VLOOKUP(cell_to_translate,{"Very Satisfied",5;"Neutral",3;"Strongly
Agree",5;"Agree",4;"Somewhat Satisfied",4},2,FALSE)

Or, if you create the same lookup array in columns A and B, with each
cell in column A having your text  and the associated cell in column B
having its numeric  value, you could simply do:

=VLOOKUP(cell_to_translate,A:B,2,FALSE)