View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Jackpot Jackpot is offline
external usenet poster
 
Posts: 46
Default Search Macro or VB script

Hi Evan

Try the below in a new workbook.

Sub Macro()
Dim strFile As String, wb As Workbook, ws As Worksheet
Dim strSearch As String, varFound As Variant
Dim intCount As Integer, wsMain As Worksheet

strSearch = InputBox("Enter Search string")
strfolder = "D:\"

Set wsMain = ActiveSheet
strFile = Dir(strfolder & "*.xl*", vbNormal)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Do While strFile < ""
Set wb = Workbooks.Open(strfolder & strFile, ReadOnly:=True)
For Each ws In wb.Worksheets
Set varFound = ws.Cells.Find(strSearch, _
LookIn:=xlValues, LookAt:=xlPart)
If Not varFound Is Nothing Then
intCount = intCount + 1
wsMain.Range("A" & intCount) = strFile
wsMain.Range("B" & intCount) = ws.Name
wsMain.Range("C" & intCount) = varFound.Address
wsMain.Range("D" & intCount) = varFound.Text
End If
Next
wb.Close False
Set wb = Nothing
strFile = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

"Evan" wrote:

anyone know of a search macro or VB script that can perform searches for cell
entrys such as SKUs along multiple Excel spreadsheets? I need a fast way to
search for specific entrys in hundreds of spreadsheets.

Thanks Evan