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!

score 0 · Answer 1 · answered Jun 17 '15 at 14:04

0

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

answered Jun 17 '15 at 14:04

Evan

600
2
7
34

Thanks for the quick reply. – Stefan89BEG Jun 17 '15 at 16:29

score 0 · Answer 2 · answered Jun 17 '15 at 16:33

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.

I'm not sure on this but you might not have to pass ranges, that might be done down stream from your function call. Have you tired passing strings without the `range()` function? — Evan, Jun 17 '15 at 16:49
No, I have not. I will post it a part of a code, I am close to make it work, but from some reason debugger throws me an error. — Stefan89BEG, Jun 17 '15 at 17:39

score 0 · Answer 3 · answered Jun 17 '15 at 17:44

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

How to populate word template from LOOKUP function from excel

3 Answers3