Sub CommandButton1 help

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[Guest]

Cheers

This worked a dream.

Monty

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

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