Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 18th 05, 05:25 PM posted to microsoft.public.excel.misc
Nigel
 
Posts: n/a
Default 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

  #2   Report Post  
Old November 18th 05, 05:27 PM posted to microsoft.public.excel.misc
Nigel
 
Posts: n/a
Default macro to find cell content in sheets and make sheet active

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

  #3   Report Post  
Old November 18th 05, 05:52 PM posted to microsoft.public.excel.misc
TomHinkle
 
Posts: n/a
Default macro to find cell content in sheets and make sheet active

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

  #4   Report Post  
Old November 18th 05, 09:55 PM posted to microsoft.public.excel.misc
Nigel
 
Posts: n/a
Default macro to find cell content in sheets and make sheet active

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

  #5   Report Post  
Old June 26th 14, 02:38 PM
Junior Member
 
First recorded activity by ExcelBanter: Feb 2012
Posts: 1
Default

Quote:
Originally Posted by Nigel View Post
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?
Nigel,

If you are using VBA, once you find the worksheet, you need to select it to make it appear. If sheetIndex is a variable containing the name or number of the worksheet, use this:

Sheets(sheetIndex).Select()

If you post your code, perhaps the forum can provide more detailed help.

Regards.

G

Last edited by Chris Gidman : June 26th 14 at 02:44 PM


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
In Excel, how do you make one whole sheet equal to another. ryan Excel Discussion (Misc queries) 2 August 31st 05 07:03 PM
Lookup values in multipul sheets and show value in another sheet Kim Excel Worksheet Functions 3 June 17th 05 01:56 PM
Copying multiple sheets from one book 2 another and undertake spec Pank Mehta Excel Discussion (Misc queries) 14 March 16th 05 05:41 PM
Linking sheets to a summary sheet in workbook gambinijr Excel Discussion (Misc queries) 4 December 16th 04 09:13 PM
i need to make the sheets name come from a cell sam wildig New Users to Excel 8 November 29th 04 03:26 PM


All times are GMT +1. The time now is 02:26 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017