Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Thanked 0 Times in 0 Posts

    Question Unable to get the Vlookup property of the WorksheetFunction Class within Word Macro

    Hello, This is my first post at this forum. Thank you for hosting it and ahead of time, forgive me if there is something stupid in what I'm trying to do.

    My WORD document contains a bookmark named Teacher. There will be a teacher's last name there. I want the macro to find that name and lookup values for the classes that the teacher is teaching from an Excel spreadsheet. Then I want to write that value in a different bookmark in the word document.

    Here is my bit of code:

    Option Explicit
    Sub ScheduleLookup()

    Dim Teacher As String
    Dim objRange As Object
    Dim wsht As Object
    Dim Period1 As String
    Dim Per1 As String

    'Find the teacher's name from the bookmark named Teacher in this Word Document
    Teacher = ActiveDocument.Bookmarks("Teacher").Range.Text

    'Teacher = "Black"
    'MsgBox Teacher

    Set wsht = GetObject("I:\Pat's Files\word macro for Julie\Test Excel File.xls").Sheets("sheet1")
    Period1 = wsht.Application.WorksheetFunction.VLookup("*" & Teacher & "*", wsht.Range("$A$3:$K$5"), 2, 0)

    Per1 = Period1

    'Write the valued of Per1 in the bookmark named Period 1
    Set objRange = ActiveDocument.Bookmarks("Period1").Range
    objRange.Text = Per1
    ActiveDocument.Bookmarks.Add "Period1", objRange
    Set wsht = Nothing
    End Sub

    I have no trouble looking up the value of the teacher bookmark so the definition of Teacher works. But, when the WorksheeFunction line is performed, I get the error "Unable to get the vlookup....". I know, it is because the VLookup didn't succeed.

    Now, if I remove the comment indicators on these lines:

    'Teacher = "Black"
    'MsgBox Teacher

    Now, I'm directly setting the value of Teacher, the vlookup succeeds without a hitch.

    It is driving me crazy. You've noticed that I have msgboxes displaying the value of the 'teacher' and to me, they're displaying the exact same word, that being 'Black' in this example.

    Any ideas?
    Last edited by PRIreland; 2015-01-11 at 19:33.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Melbourne, Victoria, Australia
    Thanked 295 Times in 267 Posts
    Does the bookmark include a paragraph mark or some other 'invisible' characters?
    Try the following to see how many characters are in that variable.
    MsgBox Len(Teacher)
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    New Lounger
    Join Date
    Jan 2015
    Thanked 0 Times in 0 Posts

    Thumbs up

    That was the problem! The "teacher" that came from the bookmark has six characters and the "teacher" that came from my manual entry has 5!

    I deleted my bookmark and added it again and now it works perfectly! I wonder what I did to put that bad character in there.

    Thank you very much, it was such an obvious problem but it took a little to find it.

    Last edited by PRIreland; 2015-01-12 at 05:27. Reason: Add final results

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts