help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Monty - 20 Sep 2007 08:20 GMT

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

Reply to this Message

Bob Phillips - 20 Sep 2007 08:54 GMT

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)

Signature

---
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
[quoted text clipped - 46 lines]
> End If
> End Sub

Reply to this Message

Monty - 20 Sep 2007 09:48 GMT

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.

> Change
>
[quoted text clipped - 72 lines]
> > End If
> > End Sub

Reply to this Message

Bob Phillips - 20 Sep 2007 10:40 GMT

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

Signature

---
HTH

Bob

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

> 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
[quoted text clipped - 86 lines]
>> > End If
>> > End Sub

Reply to this Message

Monty - 20 Sep 2007 14:20 GMT

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

> It shouldn't, it should just look in column 3.
>
[quoted text clipped - 88 lines]
> >> > End If
> >> > End Sub

Reply to this Message