Check this out and let me know !! Regards, Mandeep baluja
Sub SearchDir()
Dim i As Long: i = 2 'A variable to paste values in main
sheet
Dim Searchword As Variant 'Search value to be used
Dim Wbmain As Workbook 'Main workbook
Dim fld As Object 'fso object
On Error GoTo Tech: 'On error Go to
@mandeep.bal...@snapdeal.com
'Turn off Flickering
Application.ScreenUpdating = False: Application.DisplayAlerts = False
Set Wbmain = ThisWorkbook
Wbmain.Sheets("Sheet2").Cells.ClearContents
Wbmain.Sheets("Sheet2").Range("A1:C1") = Array("Filename", "Sheetname",
"Cellnumber")
Searchword = Wbmain.Sheets("Sheet1").Range("A3")
Wbmain.Sheets("Sheet2").AutoFilterMode = False
'Set path of Folder
Set fld = Application.FileDialog(msoFileDialogFolderPicker)
fld.Show
fldpath = fld.SelectedItems(1) & "\"
fname = Dir(fldpath)
'Looping of Files
Do While fname <> ""
Set wbtemp = Workbooks.Open(fldpath & fname)
For Each ws In wbtemp.Sheets
Wbmain.Sheets("Sheet2").Cells(i, 2).Value = ws.Name
Wbmain.Sheets("Sheet2").Cells(i, 1).Value = fname
ws.Activate
Set f = ws.Cells.Find(what:=Searchword,
LookIn:=xlValues, lookat:=xlWhole)
If Not f Is Nothing Then
k = f.Address
Add = f.Address
Do
Set f = ws.UsedRange.FindNext(f)
If f.Address <> k Then
Add = Add & "," & f.Address
End If
Loop While Not f Is Nothing And k <> f.Address
Wbmain.Sheets("sheet2").Cells(i, 3).Value =
Add
End If
i = i + 1
Next
fname = Dir()
wbtemp.Close
Loop
Wbmain.Sheets("sheet2").Activate
lr = Wbmain.Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Wbmain.Sheets("Sheet2").Range("A1:C" & lr).AutoFilter field:=3,
Criteria1:=""
Wbmain.Sheets("Sheet2").Range("A2:A" &
lr).SpecialCells(xlCellTypeVisible).Select
Selection.Rows.Delete
Wbmain.Sheets("Sheet2").AutoFilterMode = False
Exit Sub
Tech:
MsgBox "An unexpected error has occured You have done something Wrong
Contact mandeep.bal...@snapdeal.com"
End Sub
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Question Sheet.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12
