I have a workbook with a number of sheets, on each sheet there is three
columns Date Amount Po Number. This vba is supposed to only to look in
column three (Po Number). Po Numbers have six digits. The problem I have with
this
vba is if you only input 2 digits it will look over the whole workbook
looking in nearly every cell, as there is over 600,000 records. This can take
a long time to run. Is there anyway I could adapt the Macro or escape the
program if I input less than 6 digits.

Thanks

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

This is your first If-Then statement...

If WhatToFind < "" And Not WhatToFind = False Then

Change it to this...

If WhatToFind Like "######" And Not WhatToFind = False Then

although I am not exactly sure what the second expression is supposed to be
doing for you... the only time (Not WhatToFind = False) will be true is if
WhatToFind equals -1. The new first expression I gave you will only be true
if WhatToFind is composed of exactly 6 digits... I would think that test all
by itself would be enough for your requirements.

Rick


"Monty" wrote in message
...
I have a workbook with a number of sheets, on each sheet there is three
columns Date Amount Po Number. This vba is supposed to only to look
in
column three (Po Number). Po Numbers have six digits. The problem I have
with
this
vba is if you only input 2 digits it will look over the whole workbook
looking in nearly every cell, as there is over 600,000 records. This can
take
a long time to run. Is there anyway I could adapt the Macro or escape the
program if I input less than 6 digits.

Thanks

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

Cheers

This worked a dream.

Monty

"Rick Rothstein (MVP - VB)" wrote:

This is your first If-Then statement...

If WhatToFind < "" And Not WhatToFind = False Then

Change it to this...

If WhatToFind Like "######" And Not WhatToFind = False Then

although I am not exactly sure what the second expression is supposed to be
doing for you... the only time (Not WhatToFind = False) will be true is if
WhatToFind equals -1. The new first expression I gave you will only be true
if WhatToFind is composed of exactly 6 digits... I would think that test all
by itself would be enough for your requirements.

Rick


"Monty" wrote in message
...
I have a workbook with a number of sheets, on each sheet there is three
columns Date Amount Po Number. This vba is supposed to only to look
in
column three (Po Number). Po Numbers have six digits. The problem I have
with
this
vba is if you only input 2 digits it will look over the whole workbook
looking in nearly every cell, as there is over 600,000 records. This can
take
a long time to run. Is there anyway I could adapt the Macro or escape the
program if I input less than 6 digits.

Thanks

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