?
Solved

Searching a selected text in Excel workbook

Posted on 2011-10-12
10
Medium Priority
?
261 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:Lawlords
  • 7
  • 3
10 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 36956941
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.
0
 

Author Comment

by:Lawlords
ID: 36957020
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36957065
Thanks, Lawlords. Which line is it on?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 26

Expert Comment

by:redmondb
ID: 36957092
Please ignore my last post  - I'm getting it too.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36957285
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.
0
 

Author Comment

by:Lawlords
ID: 36957502
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?

0
 
LVL 26

Expert Comment

by:redmondb
ID: 36957574
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.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 36957683
Lawlords,
 
Sub fred()
Dim XL As Object
Dim KW As String
Dim xlRange As Excel.Range
Dim xlSheet As Excel.Worksheets

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

'Debug.Print KW

For i = 1 To XL.ActiveWorkbook.Worksheets.Count
 
    Set xlRange = XL.ActiveWorkbook.Worksheets(i).UsedRange.Find(KW)
    If xlRange Is Nothing Then
'        Debug.Print XL.ActiveWorkbook.Worksheets(i).Name
    Else
        XL.ActiveWorkbook.Worksheets(i).Activate
        xlRange.Activate
        Exit For
    End If
Next

End Sub

Open in new window

Regards,
Brian
0
 

Author Closing Comment

by:Lawlords
ID: 36957751
Thanks very much Brian. That works great.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36957818
Thanks, Lawlords!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question