How do I search for multiple string values in a cell using instr?

right now I got something like this but its long and messy. I need to see if the cell contains x1 or G

 If [InStr(1, ws.Range("a" & k), "x1") or InStr(1, ws.Range("a" & k), "G")] And ws.Range("G" & k) = "Lintel" Then
  k = k   1
      ElseIf (InStr(1, ws.Range("a" & k), "x1") = False) And (ws.Range("G" & k) = "Lintel") Then
      
      gfexternallintels.Range("B" & (j)).Value = ws.Range("A" & k).Value
         
     j = j   1
    End If

CodePudding user response：

(1) The first parameter of Instr is optional, it makes the code more readable if you omit it.
(2) InStr returns an integer, not a bool, you should check for it's numeric value, not for True or False (your code shoudn't rely on the internal implementation of True and False)
(3) No, there is no build-in function that can check for multiple strings at once. You could write your own function that would get an Array as 2nd argument ("needles") and loop over it.
(4) You should avoid accessing the same cell multiple times.
(5) If you want to avoid messy code, split tasks. Write intermediate values into local variables.

One attempt (there are a lot of different ways, it's a matter of personal taste)

Dim isX1 As Boolean, isG As Boolean, isLintel As Boolean
isX1 = (InStr(ws.Range("A" & k), "x1") > 0)
isG = (InStr(ws.Range("A" & k), "G") > 0)
isLintel = (ws.Range("G" & k) = "Lintel")

If (isX1 Or isG) And isLintel Then
    (...)
ElseIf Not isX1 And isLintel Then
    (...)

CodePudding user response：

I found a way with ifs

  If InStr(1, ws.Range("a" & k), "x1") And ws.Range("G" & k) = "Lintel" Then
  k = k   1
  
   ElseIf InStr(1, ws.Range("a" & k), "G") And ws.Range("G" & k) = "Lintel" Then
  k = k   1
      
      ElseIf (InStr(1, ws.Range("a" & k), "x1") = False) And (ws.Range("G" & k) = "Lintel") Then
      
      gfexternallintels.Range("B" & (j)).Value = ws.Range("A" & k).Value
         
     j = j   1
    End If