A Word Macro to VLookup a Range in Excel

[ak_edm]

Hi,

With a combination of VLOOKUP and MATCH formulas in Excel I'm able to grab a
cell's value from a multi-row, multi-column range. But I can do this only
from within Excel. The formulas look like these:


=IF($H$4="",0,VLOOKUP($H$4,$A$1:$E$50,MATCH(IF($I$4="","REG",$I$4),$A$1:$E$1,0),0)*IF($J$4="","1",$J$4))

and


=IF(H4="",0,(VLOOKUP(H4,$A$1:$E$50,MATCH("REG",$A$1:$E$1,0),0)-VLOOKUP(H4,$A$1:$E$50,MATCH(IF(I4="","REG",I4),$A$1:$E$1,0),0))*IF(J4="","1",J4))

Now I dont think the particulars of what cells are what are important, but I
use these formulas to grab descriptions and prices of items based on product
numbers and price breakpoints.

I'd like to be able to use formfields in Word to accomplish the same thing.
For example, I enter a product code in a field bookmarked "productcode", and
in another Word formfield say called "description" will pop in the product
description.

I'll need to access the Excel data using forumlas similar to above. What's
the best way? I've looked at VBA macros but I dont know how to direct Excel
formulas from within a Word macro this way.

Perhaps

1) use a macro to take the formfield entry (product code) into Excel and
drop that value it into cell A1;
2) have excel recalculatele itself so cell B1 now holds the description
based on A1;
3) then use the Word macro to retrieve B1 and display it in the formfield
named "description".

???

Thanks.

 

[Doug Robbins - Word MVP]

You are going to be really banging your head up against the wall with that
approach.

Better to use a userform:

See the following pages of Greg Maxey's website :

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

I would have a combobox on the user form that would be populated with the
product codes and descriptions from your spreadsheet, then when an item was
selected in the combobox, I would click on a command button on the userform
and that would then add the item to a listbox on the form. When all was
done, clicking on another command button would transfer all of the items and
their descriptions from the listbox into the document. You would also have
a button on the form to delete an item from the listbox if necessary and
probaby also a control into which you could enter the quantity for each
product before so that it can also be added to the listbox with the item
itself.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com