search all sheets in book to find value and activate sheet with va
 

Hi,

i have been really struggling with this macro. i have a PO Number.
example : 4533211/NICYC

in my po book i have up to 1000 purchase orders/ each with a unigue number.
i have set up a form and this number is set under a variable called PONumber
on every PO, the number is found in cell E13

what i need to do is this.
1.open my form and enter my PO number to find
2.press apply and the macro should take the number, and look through all of
the PO's until it finds the matching number.
3. when the number is found, stop searching and make this sheet active.

i have everything else completed ie errors etc, i just cannot get this right.

please can someone help me?

regs,

NS

ps i did post this in another area but feared that it might be missed;)

search all sheets in book to find value and activate sheet with va
 

So if I understand you correctly you have a workbook with a whole pile of
sheets in it. You want to seach all of the sheets to find the sheet that has
the value you are looking for in E13? If so then...

sub Test()
call FindPOSheet("4533211/NICYC")
end

sub FindPOSheet(byval strToFind as string)
dim wks as worksheet
dim blnFound as boolean

blnfound = false
for each wks in worksheets
if wks.range("E13").value = strToFind then
blnfound = true
wks.select
exit for
endif
next wks

if blnfound = false then msgbox "No PO found"
set wks = nothing
End
--
HTH...

Jim Thomlinson


"Nigel" wrote:

Hi,

i have been really struggling with this macro. i have a PO Number.
example : 4533211/NICYC

in my po book i have up to 1000 purchase orders/ each with a unigue number.
i have set up a form and this number is set under a variable called PONumber
on every PO, the number is found in cell E13

what i need to do is this.
1.open my form and enter my PO number to find
2.press apply and the macro should take the number, and look through all of
the PO's until it finds the matching number.
3. when the number is found, stop searching and make this sheet active.

i have everything else completed ie errors etc, i just cannot get this right.

please can someone help me?

regs,

NS

ps i did post this in another area but feared that it might be missed;)

search all sheets in book to find value and activate sheet wit
 

Hi Jim,

not sure that this is the right thing i am looking for. the P orders are
number sequencially 1 to ?? ( the sheet is generated on demand to there is no
set range of numbers), the po number is found in cwll E13 on every page.
so i need the macro to search through the sheets 1 to ?? and look at this
cell. i have a form to place the po number that i need to find so this will
match the cell E13 value.


thanks, in advance,

Nigel



"Jim Thomlinson" wrote:

So if I understand you correctly you have a workbook with a whole pile of
sheets in it. You want to seach all of the sheets to find the sheet that has
the value you are looking for in E13? If so then...

sub Test()
call FindPOSheet("4533211/NICYC")
end

sub FindPOSheet(byval strToFind as string)
dim wks as worksheet
dim blnFound as boolean

blnfound = false
for each wks in worksheets
if wks.range("E13").value = strToFind then
blnfound = true
wks.select
exit for
endif
next wks

if blnfound = false then msgbox "No PO found"
set wks = nothing
End
--
HTH...

Jim Thomlinson


"Nigel" wrote:

Hi,

i have been really struggling with this macro. i have a PO Number.
example : 4533211/NICYC

in my po book i have up to 1000 purchase orders/ each with a unigue number.
i have set up a form and this number is set under a variable called PONumber
on every PO, the number is found in cell E13

what i need to do is this.
1.open my form and enter my PO number to find
2.press apply and the macro should take the number, and look through all of
the PO's until it finds the matching number.
3. when the number is found, stop searching and make this sheet active.

i have everything else completed ie errors etc, i just cannot get this right.

please can someone help me?

regs,

NS

ps i did post this in another area but feared that it might be missed;)

search all sheets in book to find value and activate sheet wit
 

Hi,

just to let you know that i figured it out...

Sub findtheorder()
On Error GoTo POerrorhandler
Dim i As Integer

PONumber = TheJobNo & "/" & repusing

For i = 1 To 1000
Worksheets(i).Activate

Range("E13").Select
ActiveCell.Select

If ActiveCell.Value = PONumber Then
Range("A22").Select
ActiveCell.Select
Unload Orderlocator
Exit Sub
End If

If ActiveCell.Value < PONumber Then
End If
Next i
Exit Sub


POerrorhandler:

MsgBox "There is not a Purchase order with the number " & PONumber & ".
Please re-enter your number and try again."
Unload Orderlocator
Orderlocator.Show


End Sub

it opens the form, i enter my number and it searches it.

thanks for you help though.

regs,

Nigel



"Nigel" wrote:

Hi Jim,

not sure that this is the right thing i am looking for. the P orders are
number sequencially 1 to ?? ( the sheet is generated on demand to there is no
set range of numbers), the po number is found in cwll E13 on every page.
so i need the macro to search through the sheets 1 to ?? and look at this
cell. i have a form to place the po number that i need to find so this will
match the cell E13 value.


thanks, in advance,

Nigel



"Jim Thomlinson" wrote:

So if I understand you correctly you have a workbook with a whole pile of
sheets in it. You want to seach all of the sheets to find the sheet that has
the value you are looking for in E13? If so then...

sub Test()
call FindPOSheet("4533211/NICYC")
end

sub FindPOSheet(byval strToFind as string)
dim wks as worksheet
dim blnFound as boolean

blnfound = false
for each wks in worksheets
if wks.range("E13").value = strToFind then
blnfound = true
wks.select
exit for
endif
next wks

if blnfound = false then msgbox "No PO found"
set wks = nothing
End
--
HTH...

Jim Thomlinson


"Nigel" wrote:

Hi,

i have been really struggling with this macro. i have a PO Number.
example : 4533211/NICYC

in my po book i have up to 1000 purchase orders/ each with a unigue number.
i have set up a form and this number is set under a variable called PONumber
on every PO, the number is found in cell E13

what i need to do is this.
1.open my form and enter my PO number to find
2.press apply and the macro should take the number, and look through all of
the PO's until it finds the matching number.
3. when the number is found, stop searching and make this sheet active.

i have everything else completed ie errors etc, i just cannot get this right.

please can someone help me?

regs,

NS

ps i did post this in another area but feared that it might be missed;)