left or right in tab

[mrinal saha]

Hi Folks,

I have a sheet with name as below: I want to set a condition if the sheet name is RCD (Non-Recallable). How to identify in vba what is the right part of the tab name.

05252011 RCD (Non-Recallable)

Thanxxxx,
Mrinal

 

[mrinal saha]

Well I am so sorry for the incomplete info...

Here it is :

L = loop for number of sheets
i = loop for Sheets("Rough"), column 1 that contains all the sheet names. And the error msg i am getting is Subscript out of range

For l = 1 To Sheets.Count
For i = 3 To Sheets("Rough").UsedRange.Rows.Count
If Sheets(l).Name = Sheets("Rough").Cells(i, 1).Value And Split(Sheets(l).Name, " ", 2)(1) = "Sale" Or _
Split(Sheets(l).Name, " ", 2)(1) = "RCD (Non-Recallable)" _
Or Split(Sheets(l).Name, " ", 2)(1) = "PDR(Non Recallable)" Then
deal = Sheets("Rough").Cells(i, 4).Value

Hope this make sense..
thanks,
Mrinal

 

[jasonb75]

From memory, Split will return that error if the current string doesn't contain the specified delimiter. i.e. if there is no single space in Sheets(l).Name

Maybe testing the name with Instr would be a safer option.

 

[Sektor]

[COLOR="Blue"]Function[/COLOR] IsRCD(Sh [COLOR="Blue"]As[/COLOR] Worksheet) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Boolean[/COLOR]
    Application.Volatile
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "RCD (Non-Recallable)"
        [COLOR="Blue"]If[/COLOR] .Test(Sh.Name) [COLOR="Blue"]Then[/COLOR] IsRCD = [COLOR="Blue"]True[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Rick Rothstein]

From memory, Split will return that error if the current string doesn't contain the specified delimiter. i.e. if there is no single space in Sheets(l).Name.

Ahh, yes, that may be the problem. I got the impression from Mrinal that all of the sheets she would be testing had a "date" followed by a space followed by more text. There are ways around that problem using Split, but the InStr approach would be more direct in this case.