VBA Question with If Else, AND

[sgutknecht]

This is the 1st time I have posted, so if this is a duplicate I apologize. I am new to VBA and I have the coding below. What I am trying to have happen is, if cell p5 contains "CONSOL" to name the tab one name and if does not, name it something else. I am doing something wrong as all of the results are based upon the else part of the statement. Any assistance that I could recieve would be appreciated.

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
If Len(ws.Range("P5")) > 0 And ws.Range("P5") Like "CONSOL" Then
ws.Name = Mid((ws.Range("P5").Value), 12, 30)
Else:
ws.Name = Mid((ws.Range("P5").Value), 19, 30)
End If

On Error GoTo 0
If ws.Name <> Mid((ws.Range("P5").Value), 19, 30) Or Mid((ws.Range("P5").Value), 12, 30) Then
MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
End If
Next
End Sub

 

[alansidman]

change this line of code

[COLOR=#333333]If Len(ws.Range("P5")) > 0 And ws.Range("P5") Like "CONSOL" Then[/COLOR]

to

[COLOR=#333333]If Len(ws.Range("P5")) > 0 And ws.Range("P5") Like "*CONSOL*" Then[/COLOR]

Note the wildcard asterisks

 

[sgutknecht]

Thank you for the assistance! I had an additional issue of having a ":" in the cell that I wanted to name the tab. I figured out how to correct that and now my macro works.