How to populate word template from LOOKUP function from excel

Question

If there a possibility to insert LOOKUP function to .TypeText section?

Now, I've created another cell which is basically lookup function created on A1 cell. And that is working 100%.

I want to remove that whole column of lookups, and incorporate it directly to code.

Idea: From cell A1, do lookup in another sheet and pull some value, and that value import in word through bookmark.

Now:

.GoTo What:=wdGoToBookmark, Name:=BOOKMARK8  
      .TypeText personList(i, 8)`

My Idea:

.GoTo What:=wdGoToBookmark, Name:=BOOKMARK8  
      .TypeText Text:= LOOKUP((i, 1),Sheet2!A:A,Sheet2!B:B)`

Thanks.

p.s just tell me if I was clear, otherwise I will attach .xls or full code!

Answer 1

Here is the part of the code.

 For i = 2 To total      'for each person: open the template file and update details


    Set wDoc = wApp.Documents.Open(FILE_PATH & FILE_NAME & FILE_EXT, ReadOnly:=True)
    With wApp.Selection
     datPremFormat = Cells(i, 11).Value
     mesDanaKamFormat = Cells(i, 12).Value



           x = Application.WorksheetFunction.Index(Sheets("Sheet3").Range("$C$2:$C$1000"), Application.WorksheetFunction.Match(Sheets("Sheet1").Range("B" & i), Sheets("Sheet3").Range("$A$2:$A$1000"), 0), 1)




        .GoTo What:=wdGoToBookmark, Name:=BOOKMARK  'go to Bookmark "FirstName"
        .TypeText personList(i, 1)                  'type the value from column 2




End With
    With wDoc   'sava and close the new Word file
        .SaveAs2 FILE_PATH & " person " & personList(i, 1) & FILE_EXT
        .Close
    End With
Next
wApp.Quit
Set wDoc = Nothing
Set wApp = Nothing
MsgBox "Created " & total & " files in " & FILE_PATH

So, the whole point: x string is working perfect, I just checked it through MsgBox. Aparently, I can't put it right because of this for loop.

Run-time error:1004 Unable to get the Match property of the WorksheetFunction class

Answer 2

Thanks for the quick reply. However I've decided to insert INDEX&MATCH instead of LOOKUP.

I found some code on the web, and I was testing it with small adjustments.

Sub Test()
Dim x As String


Dim ws1, ws2 As Worksheet
Dim i As Long
Dim limit As Range
Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

With ws1




On Error Resume Next
x = Application.WorksheetFunction.Index(Range("Sheet2!$B$1:$B$5"), Application.WorksheetFunction.Match(Range("Sheet1!$A$1"), Range("Sheet2!$A$1:$A$5"), 0))

'=INDEX(Sheet2!$B$1:$B$5;MATCH(Sheet1!A1;Sheet2!$A$1:$A$5;0))'

If Err = 0 Then
    ws1.Range("B1") = x
Else
    MsgBox "Not found"
    Err.Clear
End If
On Error GoTo 0

End With

End Sub

But with no luck. Formula that I put it in a comment is working perfectly, now I am trying to incorporate it in vba shape.

Thanks.

Answer 3

You are looking for WorkSheetFunction.LOOKUP

Any function you can use in a worksheets' formula bar can be used in VBA following WorkSheetFunction

Here is MSDN on the WorkSheetFunction object