Scanning for specific sheet names

[SMC]

Hello again

I need a code that will scan and do something on all of sheets which name begins with "%" (or something else).

It should be some loop (for instance):


For i=0 To 100
If (Worksheets("%Sheet1"),Cells(i, 1).Value = "Article" Then
DO SOMETHING
End If
Next i

For i=0 To 100
If (Worksheets("%Sheet2"),Cells(i, 1).Value = "Article" Then
DO SOMETHING
End If
Next i

... and so on, just for sheets that begin with "%"


Thanks in advance

Edit: vba tags added

[lucas]

Yoinked from Malcolm:
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "%") > 0 Then
'your code below
ListBox1.AddItem sh.Name
End If
Next

[lucas]

for the rest I would need to know what this represents...range of col A??

For i=0 To 100

PS if you will select your code when you post and hit the vba button then the code will be formatted for easier reading.

[mdmackillop]

A slight change for the first characters only.
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If Left(sh.Name, 1) = "%" Then
sh.Cells(2, 1).Value = "Article"
End If
Next

[lucas]

the one I posted would find it if the the % was anywhere in the sheet name...

[lucas]

Malcolm, I'm not sure if SMC wants to look for Article in the sheet or add it as you have done...

His do something comes after the If/Then in his original post....?

[mdmackillop]

Right enough, Steve.

SMC, What are you wanting to do on each sheet? A loop may not be the best way.

[SMC]

I want to look for each appearance of "Article" in the sheet.

Then concatenate a value of the cell next to it (1 right - 'product code'
/say 7-220/), with a value of the cell "1 right, 2 down" (first color in the little column with colors (then loop for each color).

I'm planning to use multiple loops to put that concatenated content in the
first column of the "TOTAL_" sheet, and corresponding sizes and amounts in the right fields.

I have all needed loops already in my head, and i think lucas's code will solve out my problem.


Thank you very much, and if you have a better idea, it's welcome. :yes

PS
And I chose a range with up to 100 cells, because i don't expect never more than 100 rows in my order sheet (%buyerX).

PS2
Huh sorry, of course i have a userform for populating my order sheets already done. I didn't attached complete version because i'd must to translate it in english to make it clear to you what i actually want.
In fact this thread is an addition to my previous thread "A problem with user defined data type (http://vbaexpress.com/forum/showthread.php?t=10779)", but i thought i went to far from original topic, and that's why i opened a new one.

[SMC]

Oops, i haven't noticed a different ('Left') function in mdmackillop's code, that even better suits my needs.

Thanks again

[mdmackillop]

Try

[SMC]

Well Malcolm, i'm really amazed. :bow:

Just when i ran into serious trouble, losing myself in the bunch of loops, you gave me unbelievable elegant solution.

I must now study this code out (to know what i'm doing :yes).

You people here (mdmackillop, lucas, tstom, acw, xld ....) gave me so valuable help with this program , so that i'm going to name it by initial letters of those who helped so much.

It is not a phrase, i really can't thank you enough for yor engagement in this project.

Maybe i'll come back with some more questions about the code, but at this point i've simply had to express my enthusiasm and gratefulness.

Cheers
:clap:. :clap:. :clap:. :clap:

[mdmackillop]

Glad to help out.
BTW, I demerged all merged cell in my sample. Merged cell cause all sorts of problems in trying to manipulate cells in this fashion, and are best avoided.

[SMC]

Hi

Can you tell me (in english:)), how the statement:
Set tgt = Sheets("TOTAL_").Cells(Rows.Count, 1).End(xlUp).Offset(1) finds corresponding cell to fill.
Especially "Rows.Count, 1).End(xlUp)" part is not quite clear to me.

Thanks

[mdmackillop]

Tgt is a Range variable. We "Set" it to a particular range, which could be defined by a simple address or other reference.
Sheets("Total_") ensures we are dealing with the correct sheet.
The tricky bit.
If you were to record a simple macro, selecting a cell and pressing Ctrl + Up arrow you'll get something like
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/02/2007 by Malcolm
'

'
Range("F28").Select
Selection.End(xlUp).Select
End Sub

In this case, instead of using the A1 address method, we use the Cells method. The last cell in column A in Excel 2000 is A65536, in Excel 2007 it is A1048576. Rows.Count will return the correct number of rows for whichever version.
What we are saying in effect is "Go to the last cell in the column and then up to the last used cell."
Finally Offset(1) says go to the cell below. i.e. the first empty cell after the last used one.
Clear?

[SMC]

Absolutely :cool: