CODEWITHSUNDEEP
vbaexcel
IT_User
IT_User

Reputation: 779

vba if cell contains any format of string

I have been searching for the past hour as I cannot seem to find an example that goes over my use-case (although super simple in my mind). I have a for loop that searches through all the cells in a column and if a cell contains the string Open, then it adds data to a different cell. Issue I am having is sometimes the cell contains open or Open 12/12/12 etc. Currently my code I have is below. I cannot figure out how to ignore case sensitivity and if cell contains this value, to change it to "Still open or closed".

If Cells(X, C) = UCase("Closed") Then
  Cells(X, Q).Value = "Closed"
ElseIf Cells(X, C) = UCase("Open") Then
  Cells(X, Q).Value = "Still open"
End If

This works if the cell only contains "Closed" or "Open". any different spelling or additional text in the boxes causes this to fail. Is there a simple way to search if the cell contains any format of the "StRiNG" I am searching for?

Upvotes: 1

Views: 531

Answers (1)

user4039065
user4039065

Reputation:

To acccomplish pattern matching, a Select Case statement may be more appropriate.

Select Case True
    Case LCase(Cells(x, c).Value2) Like "*closed*"
        Cells(x, q) = "Closed"
    Case LCase(Cells(x, c).Value2) Like "*open*"
        Cells(x, q) = "Still Open"
    Case Else
        'something else
End Select

This will remove case sensitivity as well as searching for closed as a substring of the entire cell's contents.

Upvotes: 4

Related Questions