Create a macro which can find a record in three books
 

i havent use VB or Macro codings ever, How can i create a macro which can
find a record in three workbooks ( more than ten sheets each workbook )
and show me result data of next cell i.e. if the record found in Daily
Tracker format.xls]RMM'!$BP$21 then result will be Daily Tracker
format.xls]RMM'!$BQ$21

Create a macro which can find a record in three books
 

Try this:

Sub got_it()
Dim s As String
s = Application.InputBox("Enter search string: ")

For Each wb In Workbooks
wb.Activate
For Each sh In ActiveWorkbook.Sheets
sh.Activate
For Each r In ActiveSheet.UsedRange
If r.Value = s Then
MsgBox (wb.Name & sh.Name & r.Offset(0, 1).Address)
Exit Sub
End If
Next
Next
Next
MsgBox ("Nothing found")
End Sub

--
Gary''s Student - gsnu200723


"sand" wrote:

i havent use VB or Macro codings ever, How can i create a macro which can
find a record in three workbooks ( more than ten sheets each workbook )
and show me result data of next cell i.e. if the record found in Daily
Tracker format.xls]RMM'!$BP$21 then result will be Daily Tracker
format.xls]RMM'!$BQ$21

Create a macro which can find a record in three books
 

Thanks a lot for reply but following error accured during the operation

Run-time error 13: type mismatch


"Gary''s Student" wrote:

Try this:

Sub got_it()
Dim s As String
s = Application.InputBox("Enter search string: ")

For Each wb In Workbooks
wb.Activate
For Each sh In ActiveWorkbook.Sheets
sh.Activate
For Each r In ActiveSheet.UsedRange
If r.Value = s Then
MsgBox (wb.Name & sh.Name & r.Offset(0, 1).Address)
Exit Sub
End If
Next
Next
Next
MsgBox ("Nothing found")
End Sub

--
Gary''s Student - gsnu200723


"sand" wrote:

i havent use VB or Macro codings ever, How can i create a macro which can
find a record in three workbooks ( more than ten sheets each workbook )
and show me result data of next cell i.e. if the record found in Daily
Tracker format.xls]RMM'!$BP$21 then result will be Daily Tracker
format.xls]RMM'!$BQ$21

Create a macro which can find a record in three books
 

Are we searching for a number or text??
--
Gary''s Student - gsnu200724


"sand" wrote:

Thanks a lot for reply but following error accured during the operation

Run-time error 13: type mismatch


"Gary''s Student" wrote:

Try this:

Sub got_it()
Dim s As String
s = Application.InputBox("Enter search string: ")

For Each wb In Workbooks
wb.Activate
For Each sh In ActiveWorkbook.Sheets
sh.Activate
For Each r In ActiveSheet.UsedRange
If r.Value = s Then
MsgBox (wb.Name & sh.Name & r.Offset(0, 1).Address)
Exit Sub
End If
Next
Next
Next
MsgBox ("Nothing found")
End Sub

--
Gary''s Student - gsnu200723


"sand" wrote:

i havent use VB or Macro codings ever, How can i create a macro which can
find a record in three workbooks ( more than ten sheets each workbook )
and show me result data of next cell i.e. if the record found in Daily
Tracker format.xls]RMM'!$BP$21 then result will be Daily Tracker
format.xls]RMM'!$BQ$21

Create a macro which can find a record in three books
 

for text

"Gary''s Student" wrote:

Are we searching for a number or text??
--
Gary''s Student - gsnu200724


"sand" wrote:

Thanks a lot for reply but following error accured during the operation

Run-time error 13: type mismatch


"Gary''s Student" wrote:

Try this:

Sub got_it()
Dim s As String
s = Application.InputBox("Enter search string: ")

For Each wb In Workbooks
wb.Activate
For Each sh In ActiveWorkbook.Sheets
sh.Activate
For Each r In ActiveSheet.UsedRange
If r.Value = s Then
MsgBox (wb.Name & sh.Name & r.Offset(0, 1).Address)
Exit Sub
End If
Next
Next
Next
MsgBox ("Nothing found")
End Sub

--
Gary''s Student - gsnu200723


"sand" wrote:

i havent use VB or Macro codings ever, How can i create a macro which can
find a record in three workbooks ( more than ten sheets each workbook )
and show me result data of next cell i.e. if the record found in Daily
Tracker format.xls]RMM'!$BP$21 then result will be Daily Tracker
format.xls]RMM'!$BQ$21