Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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;) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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;) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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;) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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;) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy the same raws of all sheets from about a 100 file to a new sheet of a book and save the file | Setting up and Configuration of Excel | |||
to search and display the top 50s (from the previous 14 sheets) in a new sheet | Excel Worksheet Functions | |||
wud like 2 open the w.book of 12 sheets with my desired sheet? | Excel Worksheet Functions | |||
Find and copy across multi-sheet work book | Excel Programming | |||
macro stops copying sheets into a book after the 11th sheet | Excel Programming |