title: passing variable to Application.WorksheetFunction.VLookup, get "Unable to get the VLookup Property of the WorksheetFunction class"
body: <p>I'm having trouble with the VLookup from Word.</p>
  
  <p>I'm using the statements</p>
  
  <pre><code>set wsht = GetObject("I:\Pat's Files\word macro for Julie\Test Excel File.xls").sheets("sheet1")
  
  Period1 = wsht.Application.WorksheetFunction.VLookup("*" &amp; Teacher &amp; "*", wsht.Range("$A$3:$K$5"), 2, 0)
  </code></pre>
  
  <p>If my <code>Teacher</code> is <code>Black,Chris</code>, I want the value shown in Excel Column 2 to appear in my word macro.</p>
  
  <p>In Excel, the lookup value needs to be <code>*Black*</code> or, more precisely <code>"*" &amp; $A$9 &amp; "*"</code> where Cell A9 contains the word <code>Black</code>. </p>
  
  <p>When I run the macro as above, the system tells me that it is "Unable to get the VLookup Property of the WorksheetFunction class".</p>
  
  <p>If I run the macro with this statement:</p>
  
  <pre><code>Period1 = wsht.Application.WorksheetFunction.VLookup("*" &amp; "Black" &amp; "*", wsht.Range("$A$3:$K$5"), 2, 0)
  </code></pre>
  
  <p>I get the value that I need.  Here, I've specifically entered the word "Black" rather than had it in the variable named Teacher.</p>
  
  <p>Something is awry with the way I'm passing the variable name of teacher in the vlookup but I don't know what. How can that vlookup command be written so that a variable name can go in the Vlookup command?</p>
  
  <p>Here is my Code:</p>
  
  <pre><code>Sub Test()
  '
  ' Test Macro
  '
  '
  Dim Teacher As String
  Dim objRange
  Set wsht = GetObject("I:\Pat's Files\word macro for Julie\Test Excel File.xls").sheets("sheet1")
  
  'Find the teacher's name from the bookmark named Teacher in this Word Document
  
  Dim sMyString As String
  Teacher = ActiveDocument.Bookmarks("Teacher").Range.Text
  
  'Show me the teacher's name it found
  MsgBox Teacher
  
  'Lookup the period 1 from Excel
  Period1 = wsht.Application.WorksheetFunction.VLookup("*" &amp; Teacher &amp; "*",     wsht.Range("$A$3:$K$5"), 2, 0)
  
  Per1 = Period1
  
  'Put the value of Period 1 in the bookmark named Period1
  Set objRange = ActiveDocument.Bookmarks("Period1").Range
  objRange.Text = Per1
  ActiveDocument.Bookmarks.Add "Period1", objRange
  
  End Sub
  </code></pre>