VBA to Check if Worksheet Name Contains Text with Variable

gertie193 · Mar 28, 2019

I'm using Excel Professional Plus 2016 and I'm trying to loop through each worksheet and find the sheets named ‘(Year) Invoice’, ex: ‘2019 Invoice’, but the year is a variable—it will be entered by the user via an Input box.

The variable is YrWksht. The issue is:

a) I need wildcards around the variable to find it anywhere in the wksht name and
b) I need to find the variable and the word ‘Labor’.

Here is the excerpt of the code:

Code:

[COLOR=#222222][FONT='inherit']Dim wk as Worksheet[/FONT][/COLOR]

  [COLOR=#222222][FONT='inherit']Dim RALaborsht as Worksheet[/FONT][/COLOR]
  [COLOR=#222222][FONT='inherit']Dim YrWksht as String[/FONT][/COLOR]

  [COLOR=#222222][FONT='inherit']YrWksht = Inputbox (“Please enter year”)[/FONT][/COLOR]

[COLOR=#222222][FONT='inherit']For Each ws In Worksheets[/FONT][/COLOR]

  [COLOR=#222222][FONT='inherit']If ws.Name Like "*YrWksht*" & "*Labor*" Then ‘<==line that causes the issue[/FONT][/COLOR]
  [COLOR=#222222][FONT='inherit']Set RALaborsht = Sheets(ws.Name)[/FONT][/COLOR]
  [COLOR=#222222][FONT='inherit']End If[/FONT][/COLOR]
  [COLOR=#222222][FONT='inherit']
Next ws[/FONT][/COLOR]

I do not get an error, it just doesn’t set 'Set RALaborsht' equal to anything.

I’ve tried the line below, but I get an Error 13, type mismatch:

Code:

If ws.Name Like "" * "&YrWksht&" * "" & "*Labor*" Or ws.Name Like "" * "&YrWksht2&" * "" & "*Labor*" Then

I’ve tried using InStr, but I'm still not sure how to use wildcards with the variable or know how to use “*Labor*” as a second string to look for.

Can someone help me solve those 2 issues from a) and b) above with that line of code?

Thank you for any help.

Tom Urtis · Mar 28, 2019

There are a few errors in your code, starting with how you declared wk as worksheet but you loop with an undeclared variable named ws which indicates you do not have the strongly recommended Option Explicit statement at the top of your module to guard against this happenstance.

Also you did not say what exactly you are doing when a match is found. In any case, this code does what you probably almost want, by identifying the sheet name after assigning the RALaborsht variable to it which seemed to be your primary objective. If no such sheet animal exists, the code will tell you that as well.

Code:

Sub Test1()

Dim wk As Worksheet
Dim RALaborsht As Worksheet, blnFound As Boolean
Dim YrWksht As String
YrWksht = InputBox("Please enter year")
If Len(YrWksht) = 0 Then Exit Sub
blnFound = False

For Each wk In Worksheets
With wk
If InStr(.Name, YrWksht) > 0 And InStr(.Name, "Labor") > 0 Then
Set RALaborsht = Sheets(.Name)
blnFound = True
Exit For
End If
End With
Next wk

If blnFound = True Then
MsgBox RALaborsht.Name, , "Found"
Else
MsgBox "No such animal", , "Not found"
End If
  
End Sub

DanteAmor · Mar 29, 2019

Continuing with your idea.

YrWksht, is a variable must be outside of *.

Code:

Like "*" & [COLOR=#0000FF]YrWksht [/COLOR]& "*"

Unlike string labor, it must be within the *

Code:

"*[COLOR=#0000ff]Labor[/COLOR]*"

Code:

Sub test()
    Dim ws As Worksheet
    Dim RALaborsht As Worksheet
    Dim YrWksht As Variant
    
    YrWksht = InputBox("Please enter year")
    If YrWksht = "" Then Exit Sub
    
    For Each ws In Worksheets
        If ws.Name Like "*" & [COLOR=#0000ff]YrWksht [/COLOR]& "*" And LCase(ws.Name) Like LCase("*Labor*") Then
            Set RALaborsht = ws
        End If
    Next ws
    If Not RALaborsht Is Nothing Then
        MsgBox "Labor sheet is: " & RALaborsht.Name
    Else
        MsgBox "No match"
    End If
End Sub

gertie193 · Mar 30, 2019

Thank you very much. God bless you both/Jesus loves you.

DanteAmor · Mar 30, 2019

I'm glad to help you. Thanks for the feedback.

VBA to Check if Worksheet Name Contains Text with Variable

gertie193

Board Regular

Excel Facts

Tom Urtis

MrExcel MVP

DanteAmor

Well-known Member

gertie193

Board Regular

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

VBA to Check if Worksheet Name Contains Text with Variable

gertie193

Board Regular

Excel Facts

Tom Urtis

MrExcel MVP

DanteAmor

Well-known Member

gertie193

Board Regular

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock