macro to find cell content in sheets and make sheet active

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
0
Nigel (119)
11/18/2005 4:25:46 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
393 Views

Similar Articles

[PageSpeed] 47

Hi again,

i thought i should mention that the number of PO's is not always 1000. they 
are generated when required starting at the begining with 1 and so forth.
i have tried for i = 1 to 1000 but nothing seems to be coming to mind

regs,

NS


"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
0
Nigel (119)
11/18/2005 4:27:52 PM
use a collection..

when your workbook opens, create a collection

in the collection you can have a key and an item.

Cycle through ALL worksheets 

On each worksheet get the value of E13 and the name of the worksheet.

when you add an item to a collection, make what was in E13 the key, and the 
worksheet name the item.

then you don't have to cycle through each worksheet for every sheet.. simply 
reference the key item of the collection.

If that's a lot, then build a list of PO-numbers and their respective 
worksheet names and put them on another worksheet tab.




"Nigel" wrote:

> Hi again,
> 
> i thought i should mention that the number of PO's is not always 1000. they 
> are generated when required starting at the begining with 1 and so forth.
> i have tried for i = 1 to 1000 but nothing seems to be coming to mind
> 
> regs,
> 
> NS
> 
> 
> "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
0
TomHinkle (87)
11/18/2005 4:52:51 PM
Hi Tom,

how do i do that then? will it let me view the actual sheet on demand? if i 
am working with a specific po number, will it find the sheet and make it 
active?

i don't know how to do what you are suggesting though. i do have a macro 
that looks at another cell content but i cannot get it to work.

all i want is to enter my po number, and it will find the sheet related.


thanks in advance,


NS


"TomHinkle" wrote:

> use a collection..
> 
> when your workbook opens, create a collection
> 
> in the collection you can have a key and an item.
> 
> Cycle through ALL worksheets 
> 
> On each worksheet get the value of E13 and the name of the worksheet.
> 
> when you add an item to a collection, make what was in E13 the key, and the 
> worksheet name the item.
> 
> then you don't have to cycle through each worksheet for every sheet.. simply 
> reference the key item of the collection.
> 
> If that's a lot, then build a list of PO-numbers and their respective 
> worksheet names and put them on another worksheet tab.
> 
> 
> 
> 
> "Nigel" wrote:
> 
> > Hi again,
> > 
> > i thought i should mention that the number of PO's is not always 1000. they 
> > are generated when required starting at the begining with 1 and so forth.
> > i have tried for i = 1 to 1000 but nothing seems to be coming to mind
> > 
> > regs,
> > 
> > NS
> > 
> > 
> > "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
0
Nigel (119)
11/18/2005 8:55:04 PM
Reply:

Similar Artilces:

Adding cells by colours
Good day group, Can you guys remind me how to add cells by colours? I must have done it 10 times over but cant recall. Thanks http://www.cpearson.com/excel/colors.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "whatzzup" <whatzzup@discussions.microsoft.com> wrote in message news:2968CEDB-51EC-448A-B760-2114CB62EE03@microsoft.com... > Good day group, > > Can you guys remind me how to add cells by colours? I must have done it 10 > times over but cant recall. > Thanks ...

Excel keeps changing my formulas to include nearby cells.
Excel keeps changing my formulas to include nearby cells. More details ... -- Thanks, Shane Devenshire "emeraldc" wrote: > Excel keeps changing my formulas to include nearby cells. ...

Re: H E L P * * * Need help with question about protecting a range of cell in workbook #2
I need to know how to protect a range of cell/s in a workbook Press F1, enter protect range, click on "Lock only a few cells on a worksheet" link. Blacksmith wrote: > I need to know how to protect a range of cell/s in a workbook > > > > ...

Active sync/Outlook
Do I really have to have Office Outlook downloaded to use Active synce to back up my files from my smart phone? Or is Outlook Express the same thing? Somehow my contact list has found it's way onto Outlook Express with out me doing anything that I know of. The tech people at the phone company say Outllook Express is nothing and I have to have Office Outlook downloaded. Version of Windows? Active sync doesn't work with Outlook Express but if you use Vista, the sync center should work with Vista's Mail. -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://...

Private Activity
Hi, is it possible to "make private" (for the owner) an activity ? I.e. that others users can't read it even if they can read/modify the object for which that activity has been created ? Thanks for help, regards. Dominique Szczudlak no - only the crm security can do this ie determine if you have read privelages or not -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Dominique Szczudlak" <dszczudlak@NOaxialogSPAMfr> wrote in message news:OlIV%23zY%23EHA.2568@TK2MSFTNGP10.phx.gbl... > Hi, &g...

Hyperlink problem to cell in same workbook
I have a hyperlink created by a VB program that should link to a cell on a different worksheet within the same book. The hyperlink code currently is as follows: =HYPERLINK(ADDRESS(4,2,1,FALSE,"MultipleAliases"),"MULTIPLE DP ALIASES") MultipleAliases is a separate worksheet and I want the link to jump to row 4 cell 2 on this sheet. I realise that normally you need a spreadsheet identifier as part of the worksheet definition, thus the "MultipleAliases" would be "[FILE1.XLS]MultipleAliases" if this was saved as FILE1.XLS. If I do save this file wi...

Rename Cell Name
hey all, i have change the cell anem from A1 to StartCell. Bu, how can i rename it back to A1 or change it to another name? Thanks in advance Regards Dragon Hi Dragon go into insert / name / define - you can delete the name there and create another if you wish. Cheers JulieD "Dragon" <Dragon@discussions.microsoft.com> wrote in message news:B6A6510E-0233-4B2A-8A0C-F16F73585CBA@microsoft.com... > hey all, i have change the cell anem from A1 to StartCell. Bu, how can i > rename it back to A1 or change it to another name? > Thanks in advance > Regards > Drago...

need some help with: formatting of x2 dates in 1 cell
Hi and thanks to anyone who reads this. I have a worksheet which contains two columns of dates. In a second worksheet i have a column which adds the two dates together as TEXT and ommits dates which are blank which works perfectly, however: I would like to know how i could format each of the 2 dates in the 1 cell to have different font colors? Here is my existing cell formula: =IF('Data'!E2=0,"",(TEXT('Data'!E2,"dd/mm/yy"))&" "&IF('Data'!F2=0,"",TEXT('Data'!F2,"dd/mm/yy"))) I have a feeling its not...

Finding Transactions
I am using Money 06. Is there a way for me to locate transactions across my accounts where the 'Category' is blank? Thanks Transactions by Category report customized Category|Clear All, Category|Include unassigned income transactions, Category|Include unassigned expense transactions, Date|All Dates, Account|Select All, etc. "Bill" <Bill@discussions.microsoft.com> wrote in message news:24D7BE0D-2C92-42B2-8E58-BCF2202096D1@microsoft.com... >I am using Money 06. Is there a way for me to locate transactions across my > accounts where the 'Category' is bl...

looking for a script to make a Distribution List from a text file
Does anyone have a script for making a new Distribution List from a text file, or CSV file? I created larger dist lists by using adduser. Then, once the group is created, I mail enable it. "BwiseIT" wrote: > Does anyone have a script for making a new Distribution List from a text > file, or CSV file? > > > ...

A2 cell reference increment
I want to reference a cell using some math: $B(1+1) which I would hope equals $B2 and the cell would then contai a reference to $B2. How does one perform math funtions to the row part of a reference? I what to be able to reference a cell that contains a cell reference So Cell A1 would contain the text B12 cell A2 would reference to cell A1 and show the contents of B12. and in cell A3, I want to show the contents of B13... but I want t take the contents of cell A1 and increment it from B12 to B13... How do I do that? I tried simple math like $B(1+1) which does no equal $B2. T...

Matching Columns in work sheets and copying both rows to new
I am trying to match up two different spread sheets based on one column but to copy both rows to a new sheet. eg. First sheet has the following headings: "Owner Group " "Owner CC " "Type " "Product Categorization Tier 2 " "Product Categorization Tier 3 " "Device Name " "Status " "Model Number " "Mfg. Name " "CI ID " "Old Asset ID " "Serial Number " "Region " "Country " "Site " "Building " "Floor " "...

cell format update problem #2
say, c1, c2 is formatted as text. and a1,a2,b1, b2 are formatted as general. a1=1,a2=2, b1=1, b2 =2 and I entered c1 =a1+b1, c1 shows =a1+a2 as it is, not 3. I drag the corner of c1 and copy c1 to c2, c2 is a2+b2, So I have to F2 and enter all the cells, c1, c2. Is there a way other than "F2" and "Enter"? Consider formating c1 and c2 as general. Good Luck. "news.microsoft.com" wrote: > say, > c1, c2 is formatted as text. > and a1,a2,b1, b2 are formatted as general. > > a1=1,a2=2, b1=1, b2 =2 > > and I entered c1 =a1+b1, > c1 shows...

Extracting Data in Cells in order -- (or) eliminating empty cell space in a column
Hi I have this problem that I bet is easy to solve, but i am lost. I am an expert at the slow way to do things, but maybe there is a better way. The only way I can describe the problem is by means of an example. Lets say I have a column of numbers: >_A_|_B_| etc >> 1_1_|___| 2_3_|___| 3_2_|___| 5_5_|___| 5_3_|___| 6_4_|___| 7_7_|___| 8_3_|___| 9_1_|___| and then i write a little function in the adjoing cell, B1: =if(a1=3,a2,"") From there I fill down column B to B9. OK, pretty simple so far, right? What I am looking for is instances where I find a '3' in co...

Publisher 2007 Can i make a calender with holidays already on it?
IS there a way to make a calender in Publisher 2007 that already has holidays in it? I have a no frills 2009 calendar on my website with the holidays. http://msauer.mvps.org/publisher_projects2.htm Scroll down a little. Adding holidays is a manual operation in Publisher. -- Mary Sauer http://msauer.mvps.org/ "Jaci" <Jaci@discussions.microsoft.com> wrote in message news:1BB20065-41A5-479F-85E1-C4E91112ECBD@microsoft.com... > IS there a way to make a calender in Publisher 2007 that already has holidays > in it? ...

Where do I receive or find a delivery receipt?
To test how delivery receipt works I sent an email to myself, requesting delivery receipt, (so I know that it was received) but what form does delivery receipt come in? Same thing with read receipt, I sent myself one, respond yes when I opened the message, but haven't received anything I can see that is a read receipt. Where would I find it? Delivery receipts are generated by the recipient's mail server, if it is supported. As for the read receipt, have you hit send/receive after reading the email? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the d...

Making reminder alarm ring?
Am using Outlook 2003, with SP1. I can set reminders to make an alarm sound (like a harp), when a reminder comes due (it's in Tools > Options > Other > Advanced > Reminder Options > [X]Play sound). But Outlook has to be up and running, else no alarm sound. Is there a way to set Outlook to be sort of in the background (not just minimized, but really in the background) enough that when a reminder comes due, the alarm will sound, and either the reminder window pops up or an icon appears in bottom-right task bar? (Seems like there IS a way for it to check *e-mails* in t...

How to create treeview active control as another activex control's child?
I want to create an activex control who will wrap a treeview active control but has some its own logic. I don't know how to create it and how to set its position and size. say,if I want my active control's size is the same as the treeview active control. Any idea ? Thanks ...

Split text cell into seperate colums without splitting up a word
I have text cells with sentences ranging from 0 to 160 characters long. I want to break these into 40 character chunks (in separate cells), but don't want to split any word in half. ie, if the 40 char mark is in the middle of the work, I want to go backwards, find where the word starts and split from that point. It's exactly like a wrap text -- but I want to split those lines up into separate cells. thanks kaf If your sentence were in A1, use these 4 formulas: B1: =LEFT(A1,MAX((MID(A1,ROW(INDIRECT("1:40")),1)=" ")*ROW (INDIRECT("1:40")))) C1: =LEF...

Groups in Active Directory
I have group of people in a OU named processing. My Processing Manager is asking to use that address publically, or processing@mydomain.com, and access to the mailbox be granted to all of our processors. Secondly, this processing OU is also used for a security group that has permissions all over the network. Is there a way to get around this or do I have to recreate the OU with a slightly different name and then create and user called processing? Any help appreciated. Hi Chuy There is no problems having a User or Group with the same name as an OU. You say this OU is being used for...

Change Folder View When Using an Iframe to Display a Folder Contents
When an iFrame is being used to display the contents of a folder on a machine, is it possible to use VBScript to change the view of the folder? I've got a iFrame whose source is changed via VBScript, but when it changes the folder shows Icons. I'd rather view the folder Details instead. I don't know for sure, but I don't know of any way to access that setting in an open window. As personal settings they're not designed to be changeable by software. (Actually they're not very well designed to be changed by persons, either, but that's another story...

Cells in a Word 2007 table do not grow in length; text disappears
Hello, In MS Word 2007, I am working with a document that someone else created. There is a table in the document, and when I add text to the cells in the table, the cells do not get longer to display the text when the text reaches the bottom of the cell. Instead, the text and cursor seem to disappear behind the next cell down in the column (it goes behind the cell that is immediately below the cell that I'm typing in). How do I fix this so that the length of the cell will change as text is added to it? I want the cells to be a fixed width, but get longer if they have to...

If "text" exist within "cell" then TRUE
I cant find a way to look up if a string of text exist within a cell and reply with TRUE or FALSE, such as "if "text" exist somewhere within "cell" then TRUE". I have a list that looks something like: Archetype Skill Dancer Acrobatics, Dance, Seduction Repairman Mechanics, Technology, Security And a cell where I can define an Archetype, such as: A1=Dancer I am trying to create a formula that's something like: If "Dance" exist somewhere in "1 column from the archetype declared in A1", then type the text "Good at dancing&...

Where can I find a 2006 Business Calendar template?
I am looking for a 2006 Business Calendar template. The microsoft.com page has the 2005 Business Calendar online, but not the 2006 calendar. This shows the entire year on 1 page and is in Excel format. Thanks! Don't know which calendar template you are referring to but I created a rather flexible solution based -- rather loosely by the time I got done with it {g} -- on http://office.microsoft.com/en- us/templates/TC011585711033.aspx?CategoryID=CT011371131033 This solution allows any year between 1900 and 3000 and the first day- of-the-week can be any weekday. There is no code; ev...

Different cell heights within worksheet
I'd like to format 4 different cell heights in different worksheet columns. When I try to do so either by highlighting a single cell, row or column it changes the cell height everywhere. Row heights and column widths are properties of the entire row or column. Individual cells cannot be made higher or wider. Due to the problems they can cause I hesitate to mention "merge cells" but may be your only recourse. Gord Dibben MS Excel MVP On Tue, 11 May 2010 09:51:01 -0700, Kapoho Surfer <Kapoho Surfer@discussions.microsoft.com> wrote: >I'd lik...