The same as Wildcard replacement when using Sumproduct

[FuzzyTom]

I found this answer to a query similar to mine, Wildcard replacement when using SUMPRODUCT

I have a little different situation ... namely I am looking to find out how to apply this solution in VBA:

I have a variable ClName which is assigned to the tab name and I would like to check if this tab name, or ClName has an extension "ccs". So I did the following:

If TabName.Formula = Evaluate("=Sumproduct(--(Right(TabName,3 "=" ""ccs"")))") = True Then
NewSheet.Tab.Color = 32
Else
NewSheet.Tab.Color = 128
Endif

I get invalid qualifier ... so I tried the same argument without formula and of course it is just ignored.

Any suggestions ... thanks.

 

[Scott Huish]

Is something like this what you want?

Sub test()
Dim clName As String
clName = ActiveSheet.Name
Worksheets.Add
If Right(clName, 3) = "css" Then ActiveSheet.Tab.Color = 32 Else ActiveSheet.Tab.Color = 128
End Sub

 

[FuzzyTom]

Thanks Scott ... I will test this solution out as well.

I also got another solution ... here is the link

Wildcard replacement when using SUMPRODUCT

Thanks again.