Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


data searching

Posted on 1999-07-29
Medium Priority
Last Modified: 2010-04-30
Hello All !

how to search By Using VB in an excel file??
Question by:memory2000
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment

Accepted Solution

EDDYT earned 80 total points
ID: 1528252
   Option Explicit

Private Sub FindStr()
    Dim RngFound As Range, SearchItem As String
    Dim CellAddr As String, ExtAddr As String
    Dim RowsInRange As Integer, ColumnsInRange As Integer, TmpArray As String
    Dim WS As Worksheet, Wsh As Sheets
    On Error Resume Next
    Set Wsh = XlApp.Workbooks("You excel file name").Worksheets
    If Wsh Is Nothing Then GoTo ExitNow
    On Error GoTo ExitNow
    For Each WS In Wsh    ' Wsh is collection and contains sheets object
        SearchItem = "Your search item"
        Set RngFound = WS.Cells.Find(SearchItem, LookIn:=xlFormulas, searchOrder:=xlByColumns)
        If TypeName(RngFound) = "Range" Then
                With RngFound
                    TmpArray = .FormulaArray
                    CellAddr = .Address
                End With
                Set RngFound = WS.Cells.FindNext(After:=XlApp.ActiveCell)
                If (RngFound Is Nothing) Then Exit Sub
                If (InStr(CellAddr, RngFound.Address) > 0) Then Exit Sub
        End If
    ' This key is already associated with an element of this collection
    If Err.Number <> 457 Then Resume Next
End Sub

By the way, there is a easy way to program vb by using 'record macro function' on the word.

ie. form tool meun select record macro and then select record macro, do whatever you want and then stop record. Press Alt-F11 to view the macro.


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

661 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