Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Monty - 20 Mar 2007 09:07 GMT

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

Reply to this Message

Jim Jackson - 20 Mar 2007 13:59 GMT

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

Signature

Best wishes,

Jim

> i have the following VBA running in a workbook and i have a problem when
> running
[quoted text clipped - 40 lines]
> End If
> End Sub

Reply to this Message

Monty - 20 Mar 2007 14:37 GMT

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.
[quoted text clipped - 48 lines]
> > End If
> > End Sub

Reply to this Message

Jim Jackson - 20 Mar 2007 14:48 GMT

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.

Signature

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
[quoted text clipped - 58 lines]
> > > End If
> > > End Sub

Reply to this Message