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)

That's nice and clean, but any cells where the source has no data and
up as a "#N/A". I'm averaging some of the results, and the "#N/A"
fields make the averages display "#N/A" as well.

What about wrapping an IF(ISError......) around it? or an
IF(ISBLANK...)... something like that?

U can do one thing. U can define one array variable which will store all ur
required values like very satisfied, satisfied etc. Then u perform whole
operation in loop. Inside that loop if u know where to put rquied values like
satisfied, very satisfied etc. means that if it is fixed then u just write code
inside if ....Else if..... for performing the operation. It will become easy for
u. And also it makes sense for u.

If then also u get less speed then tell me, bcaz i am also working  with database
and worked with more that 2,00,000 rows at a time. And i know that how to work
with it. I have one another method. But then u just explain me ur problem in
deep.

I hope that above option work correctly for u.

You can create a function to convert the response and call it as you
step through the survey. Use Select Case inside the function so you
can assign a given value for more than one answer. Here is a snippet
to demonstrate what I'm talking about.

.
.
.
Sheets("Tally").Select
Cells(Tallyrow, 4).Value = ConvertAnswer(Cells(Emailrow, 6).Value)
.
.
.

Public Function ConvertAnswer(s As String) As Integer
Dim i As Integer

Select Case s
Case "Very Satisfied", "Strongly Agree", "Completely Solved"
i = 5
Case "Somewhat Satisfied", ...
i = 4
Case "Neutral", ...
i = 3
Case "Somewhat Unsatisfied", ...
i = 2
Case "Very Unsatisfied", ...
i = 1
Case Else
'handle error
End Select
ConvertAnswer = i
End Function

Just a comment about speed of processing your largest spreadsheet: I
notice that you select sheet "Tally", do something, select sheet
"Email", etc. This does take a lot of time. Have you thought of using
named ranges instead? Since you are working  with different cells in the
sheets, you can refer to a named range with a relative offset, and then
do what you do without ever opening the sheet.

Works like a charm for what I do. I presume you have
application.screenupdating = false and later True in your code as well.
If you do not, this will also speed up things dramatically.

Use AverageA instead. It will treat the text  values as a 0.