A Word Macro to VLookup a Range in Excel

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. 



  
0
Utf
4/22/2010 9:44:02 PM
word.vba.general 1023 articles. 1 followers. Follow

1 Replies
1230 Views

Similar Articles

[PageSpeed] 4

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

"ak_edm" <akedm@discussions.microsoft.com> wrote in message 
news:FB4FE65D-8CFA-407D-A7E3-580F653E455E@microsoft.com...
> 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.
>
>
>
> 
0
Doug
4/23/2010 12:45:58 AM
Reply:

Similar Artilces:

Formatting Lost in Excel 2007
Good afternoon, I've spent a lot of time googling but all I really came up with is that it may be due to links (which I have deleted) so hopefully someone out there will have an answer. We have a very large workbook that has been used since before 2005 that we have never had a problem with. Two months ago, we started using Excel 2007 but had no issues. In about mid-April when opening the file, all of the formatting is gone! So we assumed someone saved it as the wrong file so we copied an older version of the file and reformatted the entire thing. Saved it and reopened and no f...

macro to remove item between parenthesis
I have written a 957 page book. During the creation of the book I placed many phrases between parenthesis to help me remember characters and dates. For example I have hundreds that are similar to the following: (Amy Justice 749 -memo). I would like a macro that will allow me to delete all occurrences of (xxxxx -memo). In other words it will remove anything between parenthesis, including the parenthesis when the end of the phrase is -memo). This would be enormoulsy helpful. Otherwise I have to go through the entire book page by page searching for "-memo) and then highlighti...

Printing WORD docs from a form
I have a button on a form and I want it to print a Word mail merge document that contains data from the database that is linked to my application. I'm using the following code but always get a 5922 error - Word can't open the source... All variable names contain valid data, so that's NOT the problem. With appWord .Visible = True .Documents.Open pathdocname With .ActiveDocument.MailMerge .MainDocumentType = wdFormLetters strConnection = "DSN=MS Access Databases;" _ & "DB...

problem with BOLD. ITALIC...using Word as editor
I compose a msg using Outlook. Word is the editor. I set a word in bold ot italic. I send msg to myselg, reading in Outlook. The bold or italic is gone. What to do? Thanks, Leo Are you sending the message as text only? If so all formatting will disappear. If you want to retain your formatting you'll need to send your messages as HTML. You can check this in your Outlook options. -- Martin Sketchley | www.msketchley.pwp.blueyonder.co.uk (remove EGGBEANSANDSPAM to reply by e-mail) ...

Windows XP: Open file in read-only: Word, Excel, Visio
I'm often sharing files on a network drive with someone. Often, one of us knows that we don't need to open a file for modification, and we only need read-access. Is there a way to open a file in read-only mode so that when someone does need to open it for modification, the read-only user is not blocking that? If there is not a way to do this native to Windows XP, is there a way to do it from the three applications that we use most, namely Word, Excel, Visio? All are 2003 versions. Thx. I used to save files like this in "read only recommended" mode. File|SaveAs|Tools|Ge...

Not enough memory error trying to open a Word X file
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Error message: &quot;There is not enough memory or disk space available to complete the operation&quot; when trying to open a Word X file in Office 2008 in Leopard or Snow Leopard on iMac that works fine in Word X despite resaving a new version. Any tips? Not enough information to answer. Check that OS X and Office 2008 have the latest updates applied. Use Disk Utility to Repair Permissions. Check the access your user ID has to the system Temporary folder (don't worry about this if you ar...

Autofill
Hello. When I click the black plus mark to autofill a column, instead o calculating the formula that I entered in the first cell, it repeat the same answer as in the first cell. How to avoid this? should I try reinstalling excel software? ------------------------------- Sometimes, I get correct answers without problems. If this becomes problem if we employ the whole excel sheet? Once I used all columns an about 100 rows. After that, I have this problem. ================= eg. Let us say, that we need to multiply the first 2 columns. the correc answer needed is in the 3rd col. and the wrong a...

Skype/Excel add in
We sent our spreadsheets on a daily basis to our shops. We and our shop communicate via Skype and I want an 'add in' in our spreadsheets (For a example look at http://tinyurl.com/7ff3a I want the Callto:// commando integrated in my excel sheet. Why I thin it's possible? The Mailto commando works in Excel, it will automaticall open your mailclient. The Callto script/commando should automaticall open you Skype application and phone one of your contacts. How can I create this (Macro?) in my excel sheet. Your help is muc appreciated. Kindest regards, Jan Folme -- janfolme --------...

ACTIVATE ACCESS from EXCEL?
Can I activate an Access session (database is already open) from an Excel macro? Here some code from MVP John Green that will activate a certain database in Access. Watch the version issue. This should get you started: http://tinyurl.com/66r5x HTH Jason Atlanta, GA >-----Original Message----- >Can I activate an Access session (database is already >open) from an Excel macro? >. > ...

Cannot upgrade to Word 2007 from Word 2002 as word crashes when tr
We have a word add in that programatically inserts an auto text table then adds rows to it. This works ok in Word 2002 but occasionally crashes in Word 2007 - this is not a consistent crash when we carry out the same operation it happens in no specific sequence. The only place we are able to track it down to is the auto text table inserting rows. Word itself is crashing. We are using COM AddIn written in VB6. Does anyone know of any key differences/issues between Word 2002 and Word 2007 that could cause such an error. Or is there a better way to attach a debugger/word debugg...

Macro to split the contents in a single cell separated by "," into next cell in that column
Hi, I have sheet 1: column A column B columnC WR# Phase SP# Row2 60625 1-0110 60625RB1,60625NS1,60625GW1,60625BB1 Expected Output on clicking a button: column A column B columnC WR# Phase SP# Row2 60625 1-0110 60625RB1 Row3 60625NS1 Row4 60625GW1 Row5 60625BB1 Is there a way to split the contents in cell C2 separated by comma (,) and place it on next cell in column C itself? Ple...

Word 2007 TOC Not saving settings...
Hi there, When I use Word 2007 TOC, and I want to edit an existing TOC layout, etc. It wont remember my settings. I go to References Table of Contents Insert Table of Contents Then I make my chnages here, such as changing the levels of the various styles and how they show up Then I select OK, the TOC is created.. I then decide I don't like the changes I made, or I need to include another style So I go to References Table of Contents Insert Table of Contents The changes I made are no where to be seen. I have to start over from scratch. What am I doing wrong,...

Encoded Word Documents
We have recently purchased a new pc with windows 7 and have installed the Microsoft Office software however when I got to open a PDF or word document from a webpage a file conversion pop up box comes up and asks for me to select the encoding that makes the document readable but none of them do. even the windows default doesn't work...... You cannot open a pdf in Word. You need Adobe Reader. What you're downloading from a web page probably isn't a Word document, either. On May 27, 4:25=A0pm, Newuser <Newu...@discussions.microsoft.com> wrote: > We have rece...

Does Office 2003 Pro Excel have auto-save?
I am trying to set up Auto save in Excel 2003 Pro. I found auto recovery but that is not what I am looking for. I think it may be an add-in. Thanks! ehop, From a previous post by Dave Peterson: If you're running xl2k or earlier, install the autosave addin via windows|Start button|settings|control panel|Add or remove programs If you're running xl2002+, then if you can find a copy of the autosave addin from an earlier version, it should work ok. But xl2002+ has something called autorecovery. It's not the same as autosave, but will allow you to recover files if excel/windows c...

Where are my saved word?
I open the attachment directly, it is .doc and edit it in a loong time, what I ask is when I press save button and it show nothing so I think it succes. And I close it!! I can't find it now, could you help me ????? Thanks! It is gone. When editing an attachment, you /must/ save it to your HDD first, edit it and then reattach it. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "smezsc" <smezsc@discussions.microsoft.com> wrote in message news:3AF0D07A-D490-4819-A649-1C5FC079027F@microsoft.com... >I open the attachment dire...

Conditional import of Excel sheets
Hi, maybe someone can help me with the following: on a weekly basis I need to import 3 Excel files into an existing empty table (I run a delete query on this table before the import). The Excel files are stored in the same folder every week. Each file has a field showing [Current Year/Week No]. The three files show the current week for this year, previous year and the year before the previous year. How can you instruct MS Access (2003) to look up this field and to import only the approrpiate files (i.e. the files that refer to the reporting week)? I do have a calender function from which the u...

VBA Macro Error
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello, I�m running in a pretty bad problem: If I write in MSOffice 2008 a number followed by a letter like "5a" on a new line I get an error "This command is not available in this Version of Microsoft Word" It seems that word want�s to make a lookup for a VBA script - which is not supported anymore, so in panic they write the message on the screen. But instead I really want to write only 5a. Is there a button to switch of those interpretations? It was not an issue with 2004 and not on windows. Any...

macro protection
In my macro that I wrote, I have the password to unprotect th worksheet. I would like to keep that a secret. The button a create for my macro is protected, so it cannot be change, but the user of m workbook can go into tools>macros> and edit the macro there. Is there a way to make it so they cannot view the macro code? Data still needs to be entered in places on the worksheet, but I'v designed it so that it can only be entered in specified places -- Message posted from http://www.ExcelForum.com Hi I'm not sure about how it affects the access to code through Tools.Macros.E...

Bulk E-mail with Non-Word Attachment
I need to be able to send a bulk e-mail (not from a template) with attachments of a number of different document types to a group of contacts (could be from a Saved View or Marketing List). Direct E-mail, Quick Campaigns and Mail Merge do not seem to be able to do this. Is there another way? Thanks. Nope. Take a look at this blog http://groups.google.com/group/microsoft.public.crm/msg/0a84808e99645006?dmode=source&hl=en Cheers, Venkatesh ---- On May 16, 11:57 pm, DStella <DSte...@discussions.microsoft.com> wrote: > I need to be able to send a bulk e-mail (not from a tem...

Referencing other Office programs in a Excel VBA App
I din't like the way my previous post sounded, so i'm reposting... All, My Excel VBA programming is decent enough when I'm only working with Excel. However, I'm trying to expand my knowledge base and frequently I wish I could better interact with other Office programs, such as Outlook, in my Excel VBA apps. To better illustrate my question, here's a piece of code that works great (it populates a user form list with Outlook addresses) but I don't fully understand. Private Sub UserForm_Activate() Dim x As Integer Set objOL = CreateObject("...

Help please
When running excel (and word). A cell will suddenly fill with a series of dots and then random words like this: ...............he man place ther world Does anyone recognise this?I've just installed Norton Pro and it hasn't found any viruses. Surely though I have one. Can any one help? KLE Perhaps Speech Recognition is turned on. It might make text appear automatically. Choose Tools>Speech>Speech Recognition (if it had a check mark, it was turned on) KevinEvans wrote: > When running excel (and word). A cell will suddenly fill with a series of dots > and then ...

Word Wrap: Where is it in Word 2007?
I have Googled and Googled and cannot find any page that tells me where Word Wrap is. For such a basic feature you'd think it would be under View. Suggestions? On Mon, 18 Jan 2010 18:55:28 -0600, "Gary Gary" <blah@blah.blah> wrote: >I have Googled and Googled and cannot find any page that tells me where >Word Wrap is. For such a basic feature you'd think it would be under >View. Suggestions? The reason you aren't finding anything is that word wrap is automatic in Word unless you turn it off, or possibly if something is broken. Wha...

Getting Excel to return Hiragana using code/char functions
I have a Japanese computer which does this perfectly, but when I tried to use the worksheet on an English computer with Japanese language enabled, it only came up with errors. The code it was returning for the Japanese characters was much to low & putting in higher codes meant it didn`t recognise it. What can I do to make it work? ...

word 2002 compatibility with Windows 7
My niece is handicapped and her new computer has Office 2007 on it. She would like me to install word 2002 on her Inspiron 546. Is this possible? Yes, if it is licensed for another computer. If it is an OEM version, it cannot be installed on any but the system with which it was sold. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Sharon F" <SharonF@discussions.microsoft.com> wrote in message news:216DC8A6-4282-46A1-876D-B4D76E793F33@microsoft.com... > My niece is handicapped and her new computer ...

Crash in excel.exe
I have a workbook which I use with 3 add-ins that I wrote. Add-ins trigger beforeclose and beforesave event on different occasions. Everything seems to work fine in my personal notebook or in a test-desktop, but when I install add-ins to other computers I begin to get crashes. It usually happens when I close Excel. DrWatson Log has something like this: Application exception occurred: App: (pid=1180) When: 5/15/2003 @ 10:24:46.441 Exception number: c0000005 (access violation) *----> System Information <----* Computer Name: WIST-W2P-DZ0CMC User N...