title: read cell content in Excel, parse based on delimiter and insert into array
body: <p>I am a total novice with Excel VBA so please bear with me.</p>
  
  <p>I am trying to parse the cell values based on the delimiter ":". The value before the delimiter and after the delimiter will be stored in an array so that I can reference the array values in WHERE COL1 = [value before ":" in array] AND COL2= [corresponding value after ":" in array] clause in a SQL query.</p>
  
  <p>My data looks like below:</p>
  
  <pre><code>T:NYSE
  ABX:NASDAQ
  V:AIM
  (blank)
  009123:OTDCXE
  (blank)
  xxx:dhgjdg
  </code></pre>
  
  <p>and so on. The data is stored in at a fixed range A1:A10 but may not be contiguous.  I would want to run the SQL statement using a recordset .e.g </p>
  
  <pre><code>for i=0 to [last element in array]
   'run SELECT statement for each element (excluding nulls)
   next
  </code></pre>
  
  <p>Following is what I have done so far and it's not working:</p>
  
  <pre><code>Sub test3()
  
  
  Dim MyArray()
  'Dim MyArray()
  Dim MyRange As Range
  
  Dim RowCount As Long
  Dim ColCount As Long
  Dim R As Long
  Dim C As Long
  
  
  Set MyRange = Range("A1:a10") ' &lt;-- Adjust!!!
  RowCount = 10 ' &lt;-- Adjust as necessary
  ColCount = 1  ' &lt;-- Adjust as necessary
  
  ReDim MyArray(1 To RowCount, 1 To ColCount)
  
  For R = 1 To RowCount
      For C = 1 To ColCount
          MyArray(R, C) = MyRange(R, C).Value
      Next C
  Next R
  
   'Following was done only for debugging purpose
  For i = 1 To UBound(MyArray)
  For j = 1 To UBound(MyArray)
  Sheets(1).Cells(i + 20, j).Value = MyArray(i, j).Value
  Next j
  Next i
  
  
   End Sub
  </code></pre>
  
  <p>If anyone can point out what am I doing incorrect and provide some guidance. I have spent my past three days to figure out a solution so that i can extract all non-null values and use them in the sql statement.</p>
  
  <p>Many thanks in advance for at least looking at.</p>
  
  <p><strong>Edit 1:</strong></p>
  
  <p>Finally, did some digging around and came up with something, 98% of which works.</p>
  
  <p>Sample input:</p>
  
  <pre><code>LEG:NYSE
  LEG:TAM
  SPCC:AIM
  ONTPC:ZWSESA
  0943292:owndgt
  b:a
  
  d:ee
  
  f:aaaaaa
  </code></pre>
  
  <p>Final Output:</p>
  
  <pre><code>LEG     NYSE
  LEG     :TAM
  SPCC    C:AIM
  ONTPC   ZWSESA
  0943292 2:owndgt
  b       :a
  d       ee
  f       aa
  </code></pre>
  
  <p><em>Not sure why I am not getting the complete string after ":".</em>
  Below is my complete code for the above output</p>
  
  <pre><code>Sub test2()
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
  
  MyArray = ws.Range("A1:A10")
  
  Range("A11:A30").ClearContents
  
  
  ReDim newarr(LBound(MyArray) To UBound(MyArray))
  For i = LBound(MyArray) To UBound(MyArray)
   If MyArray(i, 1) &lt;&gt; "" Then
      j = j + 1
      newarr(j) = MyArray(i, 1)
   End If
  Next i
  ReDim Preserve newarr(LBound(MyArray) To j)
  
  
  For R = 1 To UBound(newarr) ' First array dimension is rows.
   ws.Cells(R + 12, 1).Value = newarr(R)
  Next R
  
  'Store values preceding and succeeding the delimiter in two arrays
  
  ReDim lftarray(LBound(newarr) To UBound(newarr))
  ReDim rtarray(LBound(newarr) To UBound(newarr))
  
  For i = LBound(newarr) To UBound(newarr)
   lftarray(i) = Split(newarr(i), ":")
   rtarray(i) = Right(newarr(i), InStr(1, newarr(i), ":"))
  Next i
  
  ReDim Preserve lftarray(LBound(newarr) To i)
  ReDim Preserve rtarray(LBound(newarr) To i)
  
  'Print values before ":"
  For i = LBound(lftarray) To UBound(lftarray) ' First array dimension is rows.
   ws.Cells(i + 24, 1).Value = lftarray(i)
  Next i
  
  
  'Print values after ":"
  
  For i = LBound(rtarray) To UBound(rtarray)
    ws.Cells(i + 24, 2).Value = rtarray(i)
  Next i
  
  
  End Sub
  </code></pre>
  
  <p>Edit2:</p>
  
  <pre><code>Sub test2()
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
  
  MyArray = ws.Range("A1:A10")
  
  Range("A11:A30").ClearContents
  
  
  ReDim newarr(LBound(MyArray) To UBound(MyArray))
  For i = LBound(MyArray) To UBound(MyArray)
   If MyArray(i, 1) &lt;&gt; "" Then
      j = j + 1
      newarr(j) = MyArray(i, 1)
   End If
  Next i
  ReDim Preserve newarr(LBound(MyArray) To j)
  
  
  For R = 1 To UBound(newarr) ' First array dimension is rows.
   ws.Cells(R + 12, 1).Value = newarr(R)
  Next R
  
  'Store values preceding and succeeding the delimiter in two arrays
  
  ReDim lftarray(LBound(newarr) To UBound(newarr))
  ReDim rtarray(LBound(newarr) To UBound(newarr))
  
  For i = LBound(newarr) To UBound(newarr)
   lftarray(i) = Split(newarr(i), ":")
   rtarray(i) = Right(newarr(i), Len(newarr(i)) - InStr(1, newarr(i), ":"))
  Next i
  
  ReDim Preserve lftarray(LBound(newarr) To i)
  ReDim Preserve rtarray(LBound(newarr) To i)
  
  'Print values before ":"
  For i = LBound(lftarray) To UBound(lftarray) ' First array dimension is rows.
   ws.Cells(i + 24, 1).Value = lftarray(i)
  Next i
  
  
  'Print values after ":"
  
  For i = LBound(rtarray) To UBound(rtarray)
    ws.Cells(i + 24, 2).Value = rtarray(i)
  Next i
  
  
  End Sub
  </code></pre>