• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

data searching

Hello All !

how to search By Using VB in an excel file??
1 Solution
   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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now