Help

[Monty]

i have the following VBA running in a workbook and i have a problem when
running
1. When i add another sheet to the workbook it will search the first
worksheet and a message box will appear and the correct cell will be
activated, however on the second sheet it will just go to the first cell and
no message box appears. any help please.

Option Explicit

Sub FindItAll()
Dim oSheet As Object
Dim Firstcell As Range
Dim NextCell As Range
Dim WhatToFind As Variant
WhatToFind = Application.InputBox("What are you looking for ?",
"Search", , 100, 100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[a1].Activate
Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
If Not Firstcell Is Nothing Then
Firstcell.Activate
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) & " in " &
oSheet.Name & "!" & Firstcell.Address)
On Error Resume Next
While (Not NextCell Is Nothing) And (Not
NextCell.Address = Firstcell.Address)
Set NextCell = Cells.FindNext(After:=ActiveCell)
If Not NextCell.Address = Firstcell.Address Then
NextCell.Activate
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) &
" in " & oSheet.Name & "!" & NextCell.Address)
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
End If
End Sub

 

[Jim Jackson]

Rearrange thesse lines as below and the message box will pop up with each new
sheet.

For Each oSheet In ActiveWorkbook.Worksheets
WhatToFind = Application.InputBox("What are you looking for ?",
"Search", , 100, 100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then

 

[Monty]

Thanks for this, however the message box does appear on the second worksheet
but the cell highlighted is the first one and not the cell that corresponds
with waht info was requested in the input box. i tested this by copying the
first worksheet and inputing the info i required. on the first worksheet cell
D52 was active with the message box however in the coppied worksheeet
(sheet2) the message box appeared but the cell that was active was A1 but the
info was in cell D52.

Any help please

Rearrange thesse lines as below and the message box will pop up with each new
sheet.

For Each oSheet In ActiveWorkbook.Worksheets
WhatToFind = Application.InputBox("What are you looking for ?",
"Search", , 100, 100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then

--
Best wishes,

Jim

i have the following VBA running in a workbook and i have a problem when
running
1. When i add another sheet to the workbook it will search the first
worksheet and a message box will appear and the correct cell will be
activated, however on the second sheet it will just go to the first cell and
no message box appears. any help please.

Option Explicit

Sub FindItAll()
Dim oSheet As Object
Dim Firstcell As Range
Dim NextCell As Range
Dim WhatToFind As Variant
WhatToFind = Application.InputBox("What are you looking for ?",
"Search", , 100, 100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[a1].Activate
Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
If Not Firstcell Is Nothing Then
Firstcell.Activate
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) & " in " &
oSheet.Name & "!" & Firstcell.Address)
On Error Resume Next
While (Not NextCell Is Nothing) And (Not
NextCell.Address = Firstcell.Address)
Set NextCell = Cells.FindNext(After:=ActiveCell)
If Not NextCell.Address = Firstcell.Address Then
NextCell.Activate
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) &
" in " & oSheet.Name & "!" & NextCell.Address)
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
End If
End Sub

 

[Jim Jackson]

I ran the macro in your original version and it found the proper cell and
popped up the message box for both sheets.

My first reply was the result of not understanding what you wanted. I
thought you wanted the Input box to come up with each new sheet. Your code
worked beautifully for me.
--
Best wishes,

Jim

Thanks for this, however the message box does appear on the second worksheet
but the cell highlighted is the first one and not the cell that corresponds
with waht info was requested in the input box. i tested this by copying the
first worksheet and inputing the info i required. on the first worksheet cell
D52 was active with the message box however in the coppied worksheeet
(sheet2) the message box appeared but the cell that was active was A1 but the
info was in cell D52.

Any help please

Rearrange thesse lines as below and the message box will pop up with each new
sheet.

For Each oSheet In ActiveWorkbook.Worksheets
WhatToFind = Application.InputBox("What are you looking for ?",
"Search", , 100, 100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then

--
Best wishes,

Jim

i have the following VBA running in a workbook and i have a problem when
running
1. When i add another sheet to the workbook it will search the first
worksheet and a message box will appear and the correct cell will be
activated, however on the second sheet it will just go to the first cell and
no message box appears. any help please.

Option Explicit

Sub FindItAll()
Dim oSheet As Object
Dim Firstcell As Range
Dim NextCell As Range
Dim WhatToFind As Variant
WhatToFind = Application.InputBox("What are you looking for ?",
"Search", , 100, 100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[a1].Activate
Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
If Not Firstcell Is Nothing Then
Firstcell.Activate
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) & " in " &
oSheet.Name & "!" & Firstcell.Address)
On Error Resume Next
While (Not NextCell Is Nothing) And (Not
NextCell.Address = Firstcell.Address)
Set NextCell = Cells.FindNext(After:=ActiveCell)
If Not NextCell.Address = Firstcell.Address Then
NextCell.Activate
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) &
" in " & oSheet.Name & "!" & NextCell.Address)
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
End If
End Sub