How to rename worksheet based on partial text of cell? 2

[rlee16]

I would like to rename my worksheets depending on the presence of a word in a text-filled cell A1. Basically, my problem is that I do not know the syntax to find one element of many in one cell. Do I need to parse out X from the sentence contained in A1 first?


Sub RenameSheet()

Dim wkst as Worksheet
x = "Annual"
y = "1st Quarter"

For Each wkst in Worksheets
If Range("A1") contains X Then
'what is syntax for contains?
wkst.Name = X
Next wkst
End Sub

 

[dace]

I'm not quite sure what you're looking for here, but I would suggest using the function:

InStr(1, Range("A1").Text, "text you're looking for")

This will return the position of the specified text within Range("A1"), or 0 if it's not found. You could use this in the following fashion:


Sub RenameSheet()
Dim wkst as Worksheet
x = "Annual"
y = "1st Quarter"

For Each wkst in Worksheets
If InStr(1, Range("A1").Text, x) > 0 Then
wkst.Name = X
Next wkst

End Sub

Hope this helps!

 

[rlee16]

Thank you Dace! Exactly what I needed.

Richard