MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Searching Excel from Word

  Asked By: Hasan    Date: Oct 18    Category: MS Office    Views: 941

I am really struggling with the VBA syntax to search an Excel
Spreadsheet from a Word Userform and would very much appreciate any
help anyone can give me.

I have a text box on a Word Userform that = a State name and I have
an Excel spreadsheet that has a column for every State, and on each
State row in the spreadsheet, there are 5 columns of data for each

I can't figure out how to search the spreadsheet for The State Name
and then retrieve the information in columns 4 and 5 of the line
that contains the State to populate 2 other text boxes on the Word

All I can get to work is the code below to pull a specific column,
but what I really need to do is search the spreadsheet finding a
State match and then pull the column data.

Dim myWB As Excel.Workbook
Set myWB = GetObject("D: \AORP Project\PPA_ 200610.xls" )
txtTotalVendorPmts = myWB.Sheets( "October 06").Range(" E6")
Set myWB = Nothing

The code above pulls 1 column of specific data, but again, I need to
figure out how to search the Spreadsheet looking for a match on
State that is located in txtState and then pull columns 4 and 5 on
the row I find a match on the State.

For example, txtState = "Alabama". I need to search column A1:52
for "Alabama" and then set txtField1 = cell 4 on that line and set
txtField2 = cell 5 on that line.



5 Answers Found

Answer #1    Answered By: Harriet Hughes     Answered On: Oct 18

1. try recording a macro with the filter on the country to get the items you
want and then maybe you can modify the vba  code to exactly suite your needs.

2. try recording a macro where you use find to actuallly find a country and then
use offet function to move the activecell to copy the contents

as i said i am not a VBA expert but this si what i would try..Crude but
recording and mdifying the macros have worked on innumerable occasions for me..

Answer #2    Answered By: Blandina Garcia     Answered On: Oct 18

I am a real rookie in this arena and not sure how to record what
I want to do. However, after a lot of reading, I THINK I am a little closer,
but not sure. Below is the code. When I execute the code, I get the error
message Run-time error '13' -- Type-mismatch on line 4 below (Set myRange =

Dim myWB As Excel.Workbook
Set myWB = GetObject("E:\AORP Project\PPA_200610.xls")
Dim myRange As Range
Set myRange = myWB.Sheets("October 06").Range("C5:C62")
txtTot = myWB.Application.WorksheetFunction.VLookup("Iowa", Range(myRange),
5, False)
Set myWB = Nothing

The State Names are in C5:62 (which I believe is the Range) and I want to get
the value in column  5 of the row  I found the State in.

Do you have any idea where I am going wrong?

Answer #3    Answered By: Addison Campbell     Answered On: Oct 18

I tested it, it works fine if you drop "myWB." from in front of
the "Application.WorksheetFunction..." statement. (Also if you have set
Option Explicit don't forget to declare txtTot as string.)

Answer #4    Answered By: Aaleyah Khan     Answered On: Oct 18

dear friends please help both of us as i am also stuck with this Procedure
in my case i had different sheet in a xls file as there are limitation of auto
filter . i want to developed one form
for searching  and displaying the corresponding value in the the form

Answer #5    Answered By: Jake Williams     Answered On: Oct 18

the range for a Vlookup should be a block starting from the top of the index
column and including the target column.

For example if you want to vlookup a value in column  C and the value you want
to return is in column G (the fifth col starting with G) the range would be
something like "C5:G62"

Didn't find what you were looking for? Find more on Searching Excel from Word Or get search suggestion and latest updates.