VBA Question with If Else, AND

sgutknecht

New Member
Joined
Feb 4, 2015
Messages
2
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,569
Office Version
2019
Platform
Windows
change this line of code

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

Code:
[COLOR=#333333]If Len(ws.Range("P5")) > 0 And ws.Range("P5") Like "*CONSOL*" Then[/COLOR]
Note the wildcard asterisks
 

sgutknecht

New Member
Joined
Feb 4, 2015
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,825
Members
406,501
Latest member
TheoDoc

This Week's Hot Topics

Top