MID$ Text: trying to look at 4 characters in a sheetname...

[itr674]

I'm using Mid$(ws.Name, 10, 4) in a macro to look at the "ff00"--it has worked as long as the sheet names have the exact number of character, but now I've had to add "nim" and haven't been able to work out the formula.

I am trying something like Mid$(ws.Name,Find$("_",ws.Name),4) but I don't think "Find", or the way I'm trying to use it is correct....

FFDAE_eq_ff00aem

FFGZZ_nim_ff00aem

 

[Zack Barresse]

Hi,

Can't you use Mid$(ws.Name, 11, 4), or do the names alter between the two types? You could do a quick check for it ..

<font face=Tahoma New>    <SPAN style="color:#00007F">If</SPAN> Mid$(ws.Name, 10, 1) = "_" <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

 

[Tom Urtis]

Re: MID$ Text: trying to look at 4 characters in a sheetnam

Have you tried InStr ?

Example,

If Instr(ws.Name, "ff00") > 0 Then

 

[itr674]

Re: MID$ Text: trying to look at 4 characters in a sheetnam

firefytr & Tom Urtis

There are 93 sheets in the book.

The first five characters are Job Tasks, then and underscore, then next 4 digits are used to align sheets under a heading (this is a TOC where are the sheets are hyperlinked). It's this "mid" section that's giving me problems...

The last three digits are used to align sheets under office headings (a second TOC hyperlinked to the sheets). I have worked this out by using Right$ on the Office TOC Builder.

As you see sheet names vary from 13 to 17 characters and the mid four characters vary in there location within the sheet name.

9XCVV_9al0ogs

7FGVB_9am0arw

FFGZZ_nim_ff00aem

9XCV7_eq_9ac0arw

Here is a line of the code:

If CBool(InStrB("|9al0|9am0|ff00|9ac0|", "|" & Mid$(ws.Name, 7, 4) & "|")) Then

 

[Zack Barresse]

Well, will there always be three characters after the four you are looking for? If so, maybe ..

Mid$(ws.Name, Len(ws.Name) - 6, 4)

 

[itr674]

Re: MID$ Text: trying to look at 4 characters in a sheetnam

firefytr - yes there will always be 3, giving it a shot now...

 

[itr674]

Re: MID$ Text: trying to look at 4 characters in a sheetnam

firefytr - that did it, thanks a bunch...