asked on

Searching a selected text in Excel workbook

Dear all,

I am trying to write a WORD VBA procedure to search for a phrase/keyword in any worksheets of an opened excel. To peform the function, the following steps will be taken:

1. Select the desired text/keyword in word
2. Run the macro
3. The excel will automatically search in all sheets

Its seem that the code has some sort of problem to be resolved. Can any expert give adice on this problem?

Thanks and regards

Dim XL As Object
    Dim KW As String

    Set XL = GetObject(, "Excel.Application")
       

    KW = Selection.Text

    'Debug.Print KW

    XL.Cells.Find(What:=KW, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

Open in new window

redmondb

Hi, Lawlords.

"...some sort of problem to be resolved..."

Do you get an error message, or some other indication that it hasn't worked? Please specify the details.

What version(s) of Word and Excel are you running?

Thanks,
Brian.

Lawlords

ASKER

Hi Brian,

When running the code in Word, the following message appeared.

Run-time error '91':
Object variable or With block variable not set

Thanks

redmondb

Thanks, Lawlords. Which line is it on?

redmondb

Please ignore my last post - I'm getting it too.

redmondb

Sorry for the delay.

OK, there were some problems with your syntax. Please try the following and then we can work on exactly what you need...

Sub fred()
Dim XL As Object
Dim KW As String
Dim xlRange As Excel.Range

Set XL = GetObject(, "Excel.Application")
       
KW = Selection.Text

'Debug.Print KW
        
'Set xlRange = XL.Workbooks("My_Spreadsheet.xlsm").Sheets("Sheet1").Range("A1:Q500").Find(KW)
Set xlRange = XL.ActiveWorkbook.ActiveSheet.UsedRange.Find(KW)
xlRange.Activate

End Sub

Open in new window

(This assumes that your required spreadsheet (and sheet) are currently active in Excel.)

Regards,
Brian.

Lawlords

ASKER

Thanks Brian,

I have tested the code and its seems working if the selected text could be found in the activesheet of the workbook. But the same error "Run-time error '91': Object variable or With block variable not set" still presist if the keywork is located in other sheets.

Have you got the same problem?

redmondb

Lawlords,

It's OK, it wasn;t supposed to do that - yet. I just wanted to maje sure there wasn't another problem.

Back in a minute!

Regards,
Brian.

ASKER CERTIFIED SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lawlords

ASKER

Thanks very much Brian. That works great.

redmondb

Thanks, Lawlords!