array rerun 1

[Luis939]

is there anyway to run an array once, and store a variable to each index, for example lets say i have

for i = 1 to thisworkbook.sheets.count
variable = value
next i

and then whenever i run another array with the same increment for i = 1 to thisworkbook.sheets.count
how do i have the program remember which value was stored for each index (worksheet)

 

[SkipVought]

Hi,

Do you mean, how do you record that index 1 means SheetWhatName?

For i = 1 to ActiveWorkbook.Sheets.Count
aryIdxRef = Sheets(i).Name
Next

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Hi,

Do you mean, how do you record that index 1 means SheetWhatName?

For i = 1 to ActiveWorkbook.Sheets.Count
aryIdxRef(i) = Sheets(i).Name
Next

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

well not not exactly, lets say each worksheet has a different value in say, cell A1.
now lets say i want to run a forloop again on every worksheet, knowing that value and doing something with it. I basically dont want again to record the value in the cell, i want to already have it in a variable to use again in the same 2nd for-loop,
hope that clarifies things, thanks!!

 

[SkipVought]

For i = 1 to ActiveWorkbook.Sheets.Count
aryValue(i) = Sheets(i).[A1]
Next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

and that would work for ranges as well??
so lets say in each worksheet i locate a range such as
thisworkbook.sheets(i).range("A:A&quot.find(what:="string&quot

how would i store that string to use again when i revisit the worksheet, thanks!!

 

[SkipVought]

What string are you storing?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

well i should have been more specific, lets say i want to look for a string in the form "*area????.xls".
Basically some worksheets have that string, some dont. I run one for loop to collect those strings and store them in varialbes or array indexes or whatever is best. For those that do have that string, i run a forloop again and do work with only those worksheets that have that string by using those stored variables and performing some instructions.

 

[SkipVought]

You're not collecting the strings. You already know what they are, don't you? Tiy're collecting the Sheets on which the strings reside in order to streamline future processing.

Or am I still missing sumthin?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

i guess all it is me running that for loop once to do one set of instructions involving that string, basically that string is the name of another filename, then after all that is done, i want to go back and revisit each sheet, and take that string in those worksheets and peform another seperate set of instructions, so i dont want to have to go and search for it again when i run the 2nd forloop, i want my program to remember what string is associated with each worksheet (yea i know it seems redundant to do that, but the way i have to run this program is complicated, i just cant run both sets of instructions one after the other, they must be seperate.

 

[SkipVought]

OK, then you are storing a string in an array element, the index of which referrs to the Sheet index.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

I dont know how to do that, is that what you did before?

 

[SkipVought]

i=1
For Each ws In WorkSheets
  If (MyStringIsFound) then 
    aryValue(i) = MyString
    i=i+1
  End If 
Next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[krinid]

Skip, completely unrelated to the topic, but just wanted to say thanks and have a star b/c you've inadvertently taught me a new way to reference ranges (aryValue(i) = Sheets(i).[A1]).

 

[SkipVought]

krinid,

Thanx for the STAR. Believe me when I say that I learn from these Forums too. Just yesterday, a member posted a solution for a chart that I never realized existed using a Pivot Chart. And there must be millions of tips and tricks that I would like to know that other members have tucked away.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com