title: Use array to search for CASE values Excel VBA
body: <p>I'm trying to create an array from a list of variable values within one cell. </p>
  
  <p>For example: Cell B3 contains values: 1A, 2B, 3A, 4A, 5C. </p>
  
  <p>I want to convert that data so that: myarray=("1A", "2B", "3A", "4A", "5C")</p>
  
  <p>i.e. </p>
  
  <pre><code> myarray(1) = "1A"
   myarray(2) = "2B" 
   myarray(3) = "3A"
   myarray(4) = "4A" 
   myarray(5) = "5C"
  </code></pre>
  
  <p>Then I want to use the values of the array to perform a CASE search to compile a running total for testing. </p>
  
  <p>My code thus far: </p>
  
  <pre><code>'Create subroutine that will copy and total data from worksheet 1 to worksheet 2
  Private Sub VTS()
  
  'Establish variable for CASE to search
  Dim ValR As String
  
  'Establish counter array
  Dim myarray(1 To 170)
  
  myarray(1) = Worksheets(2).Range("A7").Value
  myarray(2) = Worksheets(2).Range("A10").Value
  
  'Dim valves() As String
  'Dim thisValue As String
  
  ValR = Worksheets(1).Range("B4").Value
  
  'ValR = Split(valveString, ";")
  
  'valves = ValR
  
  'For v = 1 To UBound(valves)
  '    thisValve = valves(v)
  
  
  
  Select Case ValR
    Case "1A"
      Worksheets(2).Range("C7").Copy ' Copy current Total
      Worksheets(2).Range("A7").PasteSpecial ' Move to "Previous Total" to sum total
      myarray(1) = Worksheets(1).Range("B3").Value - Worksheets(1).Range("B2").Value
      If myarray(1) &lt; 0 Then
          myarray(1) = 1000000 + myarray(1)
      End If
      Worksheets(2).Range("B7").Value = myarray(1)
      Worksheets(2).Range("C7").Value = Worksheets(2).Range("A7").Value + Worksheets(2).Range("B7").Value
      Worksheets(2).Range("C7").Copy
      Worksheets(1).Range("B10").PasteSpecial
    Case "1B"
      Worksheets(2).Range("C10").Copy
      Worksheets(2).Range("A10").PasteSpecial
      myarray(2) = Worksheets(1).Range("B3").Value - Worksheets(1).Range("B2").Value
      If myarray(2) &lt; 0 Then
          myarray(2) = 1000000 + myarray(2)
      End If
      Worksheets(2).Range("B10").Value = myarray(2)
      Worksheets(2).Range("C10").Value = Worksheets(2).Range("A10").Value + Worksheets(2).Range("B10").Value
      Worksheets(2).Range("C10").Copy
      Worksheets(1).Range("B10").PasteSpecial
    Case Else
      MsgBox "Wrong Model Entered / Model Does Not Exist"
  End Select
  
  'Next v
  
  End Sub
  Public Sub Call_VTS()
    Call VTS
  End Sub
  </code></pre>
  
  <p>To summarize, I hope to:</p>
  
  <p>1) Build an Array from varying Cell Data</p>
  
  <p>2) Of which, each string represents a different position of the array</p>
  
  <p>3) Then run a CASE search for each position of the array</p>
  
  <p>I've been struggling with this for a couple of days. Any help is appreciated.</p>