title: Value used in formula of wrong data type
body: <p>I have been trying to figure this error out for the past few days with no luck. I am hoping one of you would be able to help. I am getting "value used in formula of wrong data type. </p>
  
  <p>Quick explanation:
  convert functions like this one to its corresponding text (20054/18393)*100.0
  the 5 digit numbers are Field IDs that refer to questions.</p>
  
  <p>ID Question
  20054 How many days of year do you work
  18393 How many days of vacation do you get a year</p>
  
  <p>The result I am trying to get to is (How many days of year do you work / How many days of vacation do you get a year) *100.0
  It could be easily done manually if it was just a hand full. I have over 2600 formulas that need to be converted.
  I created this function below which is resulting in the error mentioned in the title. Any assistance would be greatly appreciated</p>
  
  <p>Here is my function</p>
  
  <pre><code>Function Test(sInput As String) As String
  Dim i As Long
  Dim num As String
  Dim Text, a, str, shortname As String
  
  For i = 1 To Len(sInput)
      a = Mid(sInput, i, 1)
      If IsNumeric(a) Then
          num = num &amp; a
          Text = ""
      Else
          If a = "." Then
              num = num &amp; a
          Else
              'search for num value in second sheet short name
              shortname = WorksheetFunction.VLookup(WorksheetFunction.Int(num), Worksheets("questionlist").Range("A3:F2537"), 5, False)
              num = ""
          End If
  
          Text = shortname &amp; a
          shortname = ""
      End If
      str = str &amp; Text
  Next
  
  Test = str
  End Function
  </code></pre>