help

M

Monty

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
 
D

Don Guillett

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.
 
M

Monty

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 VBA’s


Thanks

Monty

Don Guillett said:
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
(e-mail address removed)
Monty said:
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
Click to expand...
Click to expand...
 
D

Don Guillett

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
 
M

Monty

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 said:
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
(e-mail address removed)
Monty said:
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
Click to expand...
Click to expand...
 
D

Don Guillett

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
(e-mail address removed)
Monty said:
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 said:
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
(e-mail address removed)
Monty said:
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
Click to expand...
Click to expand...
Click to expand...
 
M

Monty

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 said:
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
(e-mail address removed)
Monty said:
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 said:
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
(e-mail address removed)
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
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

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
(e-mail address removed)
Monty said:
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 said:
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
(e-mail address removed)
Monty said:
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

:

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
(e-mail address removed)
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help 3
help 4
Sub CommandButton1 help 2
Excel 2002: Need help with Listview 0
creating macro VB in Excel - find and move program 10
Find next without activating First cell Address 0
List Search Results in a UserForm 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0

Top