Searching Sheet Names using a wildcard

[sGraham24]

Hello. I have this code which finds a blank line, goes up one cell in col A, takes the active cell value and searches for a sheet named that value. It is not finding all the sheets because the active cell value may not be the exact same value as the sheet name. [I.E. Active Cell is "227017.0.6230295.4.1" and the Sheet name is "6230295.4.1" Note this is just one example of hundreds.]

Is there a way to use a wildcard within this code so that if the sheet name contains the active cell value it will find it?

Sub FindFirstSlinAndSearchSheetNames()
Dim sourceCol As Integer
Dim rowCount As Integer
Dim CurrentRow As Integer
Dim currentRowValue As String
Dim SlinNum As String

'column A has a value of 1
sourceCol = 1
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

'for every row, find the first blank cell and select it
For CurrentRow = 1 To rowCount
currentRowValue = Cells(CurrentRow, sourceCol).Value

If IsEmpty(currentRowValue) Or currentRowValue = "" Then
Cells(CurrentRow, sourceCol).Select
Exit For
End If
Next

Dim myText As String

ActiveCell.Offset(-1, 0).Select
myText = ActiveCell.Value

Dim ws As Worksheet
Dim foundSheet As String
foundSheet = ""

For Each ws In ThisWorkbook.Worksheets
If InStr(1, ws.Name, myText, vbTextCompare) > 0 Then
' PartOfWSName

foundSheet = ws.Name
Exit For
End If
Next ws

If foundSheet <> "*" Then
MsgBox "Found as sheet: " & foundSheet
Else
MsgBox "Not found as a sheet name."
End If

End Sub

 

[mumps]

if the sheet name contains the active cell value it will find it

Using your example where the Active Cell is "227017.0.6230295.4.1" and the Sheet name is "6230295.4.1", the sheet name will never contain the active cell value. Do you mean "if the cell value contains the sheet name"?

 

[sGraham24]

The active cell is dynamic, so that is one example where the sheet name contains part of the active cell but not all of it but it doesn't find it. Does that make sense?

Another example that DOES find the sheet is this: Active Cell is 557 and Sheet Name is SAIC 000557.

 

[mumps]

It looks like sometimes the cell value contains the sheet name and sometimes the sheet name contains the cell value. Is this correct?

 

[sGraham24]

I see what you are saying above... All or part of the active cell is in the sheet name. The active cell is what I have to search by to find the correct sheet because I will be searching that sheet for more items.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).