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

 

[jasonb75]

Exactly which part do you want returned?

 

[Rick Rothstein]

For what you asked, I think you may be looking for this...

If Right(ActiveSheet.Name, 20) = "RCD (Non-Recallable)" Then
    MsgBox "The ActiveSheet's Name ends with ""RCD (Non-Recallable)"""
End If

 

[littleiitin]

Try this

Sub SheetNameCheck()
    Dim strSheetName        As String
    
    strSheetName = ActiveSheet.Name
    
    If InStr(strSheetName, "RCD (Non-Recallable") Then
        MsgBox "Name Found"
    Else
    
    End If
End Sub

 

[Rick Rothstein]

Try this

Sub SheetNameCheck()
    Dim strSheetName        As String
 
    strSheetName = ActiveSheet.Name
 
    If InStr(strSheetName, "RCD (Non-Recallable") Then
        MsgBox "Name Found"
    Else
 
    End If
End Sub

That won't restrict itself to finding the text on the right side of the name... it will report the name found even it the searched for text is in the middle with other text surrounding it.

 

[mrinal saha]

Well I want the text after the date. But I can't give the precise number of characters beacuse it would vary tab to tab. So provide me with a dynamic solution

thanks
Mrinal

 

[Rick Rothstein]

Well I want the text after the date. But I can't give the precise number of characters beacuse it would vary tab to tab. So provide me with a dynamic solution

Okay, then you want all the text after the first space...

NameAfterDate = Split(ActiveSheet.Name, " ", 2)(1)

EDIT
Here is my originally posted code modified to use the above (in case that is actually what you are looking for)...

If Split(ActiveSheet.Name, " ", 2)(1) = "RCD (Non-Recallable)" Then
    MsgBox "The ActiveSheet's Name ends with ""RCD (Non-Recallable)"""
End If

 

[mrinal saha]

Does this line looks ok..........because it isn't working

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

 

[mrinal saha]

Error i am getting is

Subscript out of range

 

[Rick Rothstein]

It is usually a good idea to tell us more than "it isn't working" so we have an idea where to concentrate our efforts. What exactly do you mean by "isn't working"... are you getting an error message and, if so, what is it? Also, what is L equal to (that is the argument you have for your Sheets property call and how does that relate to the "i" you have in your Cells property call... are you in a double loop of some sort? Maybe if you post more of your code, it might help.