help

G

Guest

I have a workbook with a number of sheets, on each sheet there is three
column > Date > Amount > Po Number. This vba is only to look in column three
Po Number and all Po Numbers have six digits. The problem I have with this
vba is it looks at all columns and if you input 1 number it nearly looks at
every record, which is over 500,000. can anyone help me to refine this vba.
Thanks

Monty
Sub CommandButton1_Click()
Dim WhatToFind As Variant
Dim Found As Boolean
Found = False
WhatToFind = Application.InputBox("INPUT PO NUMBER ?", "Search", , 100,
100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[c1].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
Found = True
MsgBox ("PO CASHED!" & Firstcell.Text)
'On Error Resume Next
Set NextCell = Cells.FindNext(After:=ActiveCell)
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
Found = True
MsgBox ("PO CASHED!" & NextCell.Text)
Set NextCell = Cells.FindNext(After:=ActiveCell)
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
If Not Found Then
MsgBox ("PO NOT FOUND")
End If
End If
End Sub
 
B

Bob Phillips

Change

Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)

to

Set Firstcell = Columns(3).Find(What:=WhatToFind, _
LookIn:=xlValues,
_
LookAt:=xlPart,
_
SearchOrder:=xlByRows,
_
SearchDirection:=xlNext,
_
MatchCase:=False)

and then

Set NextCell = Cells.FindNext(After:=ActiveCell)
to

Set NextCell = Columns(3).FindNext(After:=ActiveCell)
--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
G

Guest

Thanks Bob I have incorporated your solution into my vba and works fine if
you put in a six digit number. However if you only put in 1 or 2 digits it
looks at every cell, within each worksheet. Any suggestions welcome.

Bob Phillips said:
Change

Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)

to

Set Firstcell = Columns(3).Find(What:=WhatToFind, _
LookIn:=xlValues,
_
LookAt:=xlPart,
_
SearchOrder:=xlByRows,
_
SearchDirection:=xlNext,
_
MatchCase:=False)

and then

Set NextCell = Cells.FindNext(After:=ActiveCell)
to

Set NextCell = Columns(3).FindNext(After:=ActiveCell)
--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



Monty said:
I have a workbook with a number of sheets, on each sheet there is three
column > Date > Amount > Po Number. This vba is only to look in column
three
Po Number and all Po Numbers have six digits. The problem I have with this
vba is it looks at all columns and if you input 1 number it nearly looks
at
every record, which is over 500,000. can anyone help me to refine this
vba.
Thanks

Monty
Sub CommandButton1_Click()
Dim WhatToFind As Variant
Dim Found As Boolean
Found = False
WhatToFind = Application.InputBox("INPUT PO NUMBER ?", "Search", , 100,
100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[c1].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
Found = True
MsgBox ("PO CASHED!" & Firstcell.Text)
'On Error Resume Next
Set NextCell = Cells.FindNext(After:=ActiveCell)
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
Found = True
MsgBox ("PO CASHED!" & NextCell.Text)
Set NextCell = Cells.FindNext(After:=ActiveCell)
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
If Not Found Then
MsgBox ("PO NOT FOUND")
End If
End If
End Sub
Click to expand...
Click to expand...
 
B

Bob Phillips

It shouldn't, it should just look in column 3.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



Monty said:
Thanks Bob I have incorporated your solution into my vba and works fine if
you put in a six digit number. However if you only put in 1 or 2 digits it
looks at every cell, within each worksheet. Any suggestions welcome.

Bob Phillips said:
Change

Set Firstcell = Cells.Find(What:=WhatToFind,
LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)

to

Set Firstcell = Columns(3).Find(What:=WhatToFind, _

LookIn:=xlValues,
_

LookAt:=xlPart,
_

SearchOrder:=xlByRows,
_

SearchDirection:=xlNext,
_

MatchCase:=False)

and then

Set NextCell = Cells.FindNext(After:=ActiveCell)
to

Set NextCell = Columns(3).FindNext(After:=ActiveCell)
--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Monty said:
I have a workbook with a number of sheets, on each sheet there is three
column > Date > Amount > Po Number. This vba is only to look in column
three
Po Number and all Po Numbers have six digits. The problem I have with
this
vba is it looks at all columns and if you input 1 number it nearly
looks
at
every record, which is over 500,000. can anyone help me to refine this
vba.
Thanks

Monty
Sub CommandButton1_Click()
Dim WhatToFind As Variant
Dim Found As Boolean
Found = False
WhatToFind = Application.InputBox("INPUT PO NUMBER ?", "Search", ,
100,
100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[c1].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
Found = True
MsgBox ("PO CASHED!" & Firstcell.Text)
'On Error Resume Next
Set NextCell = Cells.FindNext(After:=ActiveCell)
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
Found = True
MsgBox ("PO CASHED!" & NextCell.Text)
Set NextCell = Cells.FindNext(After:=ActiveCell)
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
If Not Found Then
MsgBox ("PO NOT FOUND")
End If
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Bob

This is my new VBA-- Can you please read over it to see if there is any
conflicting entries's. Sorry for the trouble.

Monty

Sub CommandButton1_Click()
Dim WhatToFind As Variant
Dim Found As Boolean
Found = False
WhatToFind = Application.InputBox("INPUT PO NUMBER ?", "Search", , 100,
100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[c3].Activate
Set Firstcell = Columns(3).Find(What:=WhatToFind,
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)

If Not Firstcell Is Nothing Then
Firstcell.Activate
Found = True
MsgBox ("PO CASHED!" & Firstcell.Text)

Set NextCell = Columns(3).FindNext(After:=ActiveCell)
While (Not NextCell Is Nothing) And (Not
NextCell.Address = Firstcell.Address)

If Not NextCell.Address = Firstcell.Address Then
NextCell.Activate
Found = True
MsgBox ("PO CASHED!" & NextCell.Text)
Set NextCell = Cells.FindNext(After:=ActiveCell)
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
If Not Found Then
MsgBox ("PO NOT FOUND")
End If
End If
End Sub


Bob Phillips said:
It shouldn't, it should just look in column 3.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



Monty said:
Thanks Bob I have incorporated your solution into my vba and works fine if
you put in a six digit number. However if you only put in 1 or 2 digits it
looks at every cell, within each worksheet. Any suggestions welcome.

Bob Phillips said:
Change

Set Firstcell = Cells.Find(What:=WhatToFind,
LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)

to

Set Firstcell = Columns(3).Find(What:=WhatToFind, _

LookIn:=xlValues,
_

LookAt:=xlPart,
_

SearchOrder:=xlByRows,
_

SearchDirection:=xlNext,
_

MatchCase:=False)

and then

Set NextCell = Cells.FindNext(After:=ActiveCell)
to

Set NextCell = Columns(3).FindNext(After:=ActiveCell)
--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



I have a workbook with a number of sheets, on each sheet there is three
column > Date > Amount > Po Number. This vba is only to look in column
three
Po Number and all Po Numbers have six digits. The problem I have with
this
vba is it looks at all columns and if you input 1 number it nearly
looks
at
every record, which is over 500,000. can anyone help me to refine this
vba.
Thanks

Monty
Sub CommandButton1_Click()
Dim WhatToFind As Variant
Dim Found As Boolean
Found = False
WhatToFind = Application.InputBox("INPUT PO NUMBER ?", "Search", ,
100,
100, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[c1].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
Found = True
MsgBox ("PO CASHED!" & Firstcell.Text)
'On Error Resume Next
Set NextCell = Cells.FindNext(After:=ActiveCell)
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
Found = True
MsgBox ("PO CASHED!" & NextCell.Text)
Set NextCell = Cells.FindNext(After:=ActiveCell)
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
If Not Found Then
MsgBox ("PO NOT FOUND")
End If
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

Sub CommandButton1 help 2
help 7
Help 3
creating macro VB in Excel - find and move program 10
Do Until Loop Dilemma 2
simple quick question 2
type mismatch here! 2
Method or data member not found 1

Top