I have the following VBA working on my excel spreadsheet which works fine.
However what I would like to do is download my daily excel sheet in relation
to cheques cashed by the bank and just add that worksheet to my workbook.
What happens then is it just searches the first worksheet and not the second
or third one. So presently I have to copy and paste the info on to the first
sheet in order for the VBA to work, Does anyone have any suggestions.


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

Without looking too closely, it appears that FINDNEXT (look in vba HELP for
an excellent example) would be better and you can just incorporate into a
for/next loop. Do it withOUT selections. Post back after you have tried
this.

--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
I have the following VBA working on my excel spreadsheet which works fine.
However what I would like to do is download my daily excel sheet in
relation
to cheques cashed by the bank and just add that worksheet to my workbook.
What happens then is it just searches the first worksheet and not the
second
or third one. So presently I have to copy and paste the info on to the
first
sheet in order for the VBA to work, Does anyone have any suggestions.


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

Sorry new user forgot to mention that when I have two or three worksheets
within a workbook the VBA works fine. I input cheque number 72158 in the
message box it will locate this in the first worksheet by going to the
correct cell in which the cheque number appears and a message box will appear
stating cheque number and cell ref. However when you click okay for it to
search the second worksheet it will come up with the message box however it
selects cell A1which is not the correct cell.
If I could fix this problem I would be happy.

VBA help is are you talking about excel help or a web page help in relation
to VBAs


Thanks

Monty

"Don Guillett" wrote:

Without looking too closely, it appears that FINDNEXT (look in vba HELP for
an excellent example) would be better and you can just incorporate into a
for/next loop. Do it withOUT selections. Post back after you have tried
this.

--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
I have the following VBA working on my excel spreadsheet which works fine.
However what I would like to do is download my daily excel sheet in
relation
to cheques cashed by the bank and just add that worksheet to my workbook.
What happens then is it just searches the first worksheet and not the
second
or third one. So presently I have to copy and paste the info on to the
first
sheet in order for the VBA to work, Does anyone have any suggestions.


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

I used the example in the vba help index FINDNEXT and modified to suit.
Notice that there are NO selections, as they are NOT necessary or desirable.
This sub may be fired from anywhere in the workbook.

Sub findem()
what = InputBox("what to find ie:100")
For Each Ws In Worksheets
'MsgBox Ws.Name

With Ws.Range("a1:a500")
Set c = .Find(what, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox what & " found at " & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Next Ws
End Sub

--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
I have the following VBA working on my excel spreadsheet which works fine.
However what I would like to do is download my daily excel sheet in
relation
to cheques cashed by the bank and just add that worksheet to my workbook.
What happens then is it just searches the first worksheet and not the
second
or third one. So presently I have to copy and paste the info on to the
first
sheet in order for the VBA to work, Does anyone have any suggestions.


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

Hi

Tryed this out this morning. error message appears.
Vaiable Not Defined.

The word highlighted is What.

Can this be run with a command button and an input box.

Thanks

Monty

"Don Guillett" wrote:

I used the example in the vba help index FINDNEXT and modified to suit.
Notice that there are NO selections, as they are NOT necessary or desirable.
This sub may be fired from anywhere in the workbook.

Sub findem()
what = InputBox("what to find ie:100")
For Each Ws In Worksheets
'MsgBox Ws.Name

With Ws.Range("a1:a500")
Set c = .Find(what, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox what & " found at " & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Next Ws
End Sub

--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
I have the following VBA working on my excel spreadsheet which works fine.
However what I would like to do is download my daily excel sheet in
relation
to cheques cashed by the bank and just add that worksheet to my workbook.
What happens then is it just searches the first worksheet and not the
second
or third one. So presently I have to copy and paste the info on to the
first
sheet in order for the VBA to work, Does anyone have any suggestions.


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

Either remove the option explicit at the top of the module or dim your
variables.
This should be in a regular module instead of a sheet module.
Run, as is, and assign to a button in forms or a drawing SHAPE as I do. I
don't like command buttons.


Sub findem()
Dim what As Variant
Dim ws As Worksheet
Dim c As Range
Dim firstaddress As Range
what = InputBox("what to find ie:100")
For Each ws In Worksheets
'MsgBox Ws.Name

With ws.Range("a1:a500")
Set c = .Find(what, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
MsgBox what & " found at " & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
Next ws
End Sub

--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
Hi

Tryed this out this morning. error message appears.
Vaiable Not Defined.

The word highlighted is What.

Can this be run with a command button and an input box.

Thanks

Monty

"Don Guillett" wrote:

I used the example in the vba help index FINDNEXT and modified to suit.
Notice that there are NO selections, as they are NOT necessary or
desirable.
This sub may be fired from anywhere in the workbook.

Sub findem()
what = InputBox("what to find ie:100")
For Each Ws In Worksheets
'MsgBox Ws.Name

With Ws.Range("a1:a500")
Set c = .Find(what, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox what & " found at " & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Next Ws
End Sub

--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
I have the following VBA working on my excel spreadsheet which works
fine.
However what I would like to do is download my daily excel sheet in
relation
to cheques cashed by the bank and just add that worksheet to my
workbook.
What happens then is it just searches the first worksheet and not the
second
or third one. So presently I have to copy and paste the info on to the
first
sheet in order for the VBA to work, Does anyone have any suggestions.


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

i have the following in colums A B C

Date Cashed Amount PO Number
220906 -9.04 "CH 651498"
130906 -105917.53 "CH 684963"
140906 -276806.25 "CH 684964"
140906 -158.93 "CH 684966"
200906 -16.39 "CH 684969"
180906 -3600.93 "CH 684970"
200906 -516.16 "CH 684971"
200906 -117.11 "CH 684972"
250906 -6631.64 "CH 684973"
260906 -96.48 "CH 689034"
220906 -3.72 "CH 689096"
180906 -9 "CH 694885"
210906 -3.51 "CH 698450"
260906 -1.46 "CH 700550"
190906 -1.48 "CH 700583"

Sorry the above are cheques cashed by the bank and i have thousands in a
workbook. when i run my VBA it finds the first one however when it finds the
second one in a diffrrent worksheet the cell is not highlighted.
when i try your vba out nothing happens.

can you please help.

Thanks

Monty

"Don Guillett" wrote:

Either remove the option explicit at the top of the module or dim your
variables.
This should be in a regular module instead of a sheet module.
Run, as is, and assign to a button in forms or a drawing SHAPE as I do. I
don't like command buttons.


Sub findem()
Dim what As Variant
Dim ws As Worksheet
Dim c As Range
Dim firstaddress As Range
what = InputBox("what to find ie:100")
For Each ws In Worksheets
'MsgBox Ws.Name

With ws.Range("a1:a500")
Set c = .Find(what, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
MsgBox what & " found at " & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
Next ws
End Sub

--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
Hi

Tryed this out this morning. error message appears.
Vaiable Not Defined.

The word highlighted is What.

Can this be run with a command button and an input box.

Thanks

Monty

"Don Guillett" wrote:

I used the example in the vba help index FINDNEXT and modified to suit.
Notice that there are NO selections, as they are NOT necessary or
desirable.
This sub may be fired from anywhere in the workbook.

Sub findem()
what = InputBox("what to find ie:100")
For Each Ws In Worksheets
'MsgBox Ws.Name

With Ws.Range("a1:a500")
Set c = .Find(what, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox what & " found at " & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Next Ws
End Sub

--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
I have the following VBA working on my excel spreadsheet which works
fine.
However what I would like to do is download my daily excel sheet in
relation
to cheques cashed by the bank and just add that worksheet to my
workbook.
What happens then is it just searches the first worksheet and not the
second
or third one. So presently I have to copy and paste the info on to the
first
sheet in order for the VBA to work, Does anyone have any suggestions.


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

I just re-tested my code and changed
Dim firstaddress As Range
to
Dim firstaddress As string

and it worked fine for 2 instances of 180906 on 2 different worksheets
MsgBox what & " found at " & ws.Name & c.Address
--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
i have the following in colums A B C

Date Cashed Amount PO Number
220906 -9.04 "CH 651498"
130906 -105917.53 "CH 684963"
140906 -276806.25 "CH 684964"
140906 -158.93 "CH 684966"
200906 -16.39 "CH 684969"
180906 -3600.93 "CH 684970"
200906 -516.16 "CH 684971"
200906 -117.11 "CH 684972"
250906 -6631.64 "CH 684973"
260906 -96.48 "CH 689034"
220906 -3.72 "CH 689096"
180906 -9 "CH 694885"
210906 -3.51 "CH 698450"
260906 -1.46 "CH 700550"
190906 -1.48 "CH 700583"

Sorry the above are cheques cashed by the bank and i have thousands in a
workbook. when i run my VBA it finds the first one however when it finds
the
second one in a diffrrent worksheet the cell is not highlighted.
when i try your vba out nothing happens.

can you please help.

Thanks

Monty

"Don Guillett" wrote:

Either remove the option explicit at the top of the module or dim your
variables.
This should be in a regular module instead of a sheet module.
Run, as is, and assign to a button in forms or a drawing SHAPE as I do. I
don't like command buttons.


Sub findem()
Dim what As Variant
Dim ws As Worksheet
Dim c As Range
Dim firstaddress As Range
what = InputBox("what to find ie:100")
For Each ws In Worksheets
'MsgBox Ws.Name

With ws.Range("a1:a500")
Set c = .Find(what, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
MsgBox what & " found at " & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
Next ws
End Sub

--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
Hi

Tryed this out this morning. error message appears.
Vaiable Not Defined.

The word highlighted is What.

Can this be run with a command button and an input box.

Thanks

Monty

"Don Guillett" wrote:

I used the example in the vba help index FINDNEXT and modified to
suit.
Notice that there are NO selections, as they are NOT necessary or
desirable.
This sub may be fired from anywhere in the workbook.

Sub findem()
what = InputBox("what to find ie:100")
For Each Ws In Worksheets
'MsgBox Ws.Name

With Ws.Range("a1:a500")
Set c = .Find(what, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox what & " found at " & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Next Ws
End Sub

--
Don Guillett
SalesAid Software

"Monty" wrote in message
...
I have the following VBA working on my excel spreadsheet which works
fine.
However what I would like to do is download my daily excel sheet in
relation
to cheques cashed by the bank and just add that worksheet to my
workbook.
What happens then is it just searches the first worksheet and not
the
second
or third one. So presently I have to copy and paste the info on to
the
first
sheet in order for the VBA to work, Does anyone have any
suggestions.


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