title: VBA Using a String Array as SubString Parameter InStr Function (Excel)
body: <p>Long time searcher, first time asker..</p>
  
  <p>Goal: 
   - loop through a column containing addresses 
   - assign a value (city name) to cell offset 0,6 based on which Zip Code the cell contains</p>
  
  <p>Here's what I've got so far (shortened array lengths):</p>
  
  <pre><code>   Sub LabelCell()
      Dim SrchRng As Range, cel As Range
      Dim ZipA() As String
      Dim ZipB() As String
      Dim ZipC() As String
      Dim ZipD() As String
  
      ZipA = Array("12345", "12346", "12347", "12348", "12349")
      ZipB = Array("22345", "22346", "22347", "22348", "22349")
      ZipC = Array("32345", "32346", "32347", "32348", "32349")
      ZipD = Array("42345", "42346", "42347", "42348", "42349")
  
      Set SrchRng = Range("D6:D350")
  
      For Each cel In SrchRng
          If InStr(1, cel.Value, ZipA()) Then
              cel.Offset(0, 6).Value = "City 1"
          ElseIf InStr(1, cel.Value, ZipB()) Then
              cel.Offset(0, 6).Value = "City 2"
          ElseIf InStr(1, cel.Value, ZipC()) Then
              cel.Offset(0, 6).Value = "City 3"
          ElseIf InStr(1, cel.Value, ZipD()) Then
              cel.Offset(0, 6).Value = "City 4"
          End If
      Next cel
  End Sub
  </code></pre>
  
  <p>As you can see, there are 4 string arrays, each containing multiple zip codes relative to its region. I've tried Declaring the Arrays as Variants and using Split to no avail. The above code gives me a Type Mismatch error and the other methods I've tried have either yielded the same or "subscript out of range"</p>
  
  <p>I'm very opposed to defining each array's length and manually assigning individual positions as the total is upwards of 400 zip codes - and more importantly, the code would look hideous.</p>
  
  <p>TLDR: Is it possible to achieve what the title suggests?</p>
  
  <p>Thanks</p>