title: Split data string over columns AND rows using VBA
body: <p>I am trying to speed up a currently working automated workbook.</p>
  
  <p>PHP sends a string similar to the below to VBA:</p>
  
  <pre><code>1[|:#:|]text-one[|:#:|]code-one[|:#:|]qty-one[{:#:}]
  2[|:#:|]text-two[|:#:|]code-two[|:#:|]qty-two[{:#:}]
  </code></pre>
  
  <p>where</p>
  
  <ul>
  <li><code>[|:#|]</code> represents "new column"</li>
  <li><code>[{:#:}]</code> represents "new row"</li>
  </ul>
  
  <p>When it is parsed by the VBA this is the output:</p>
  
  <p><img src="https://i.stack.imgur.com/k4uH9.png" alt=""></p>
  
  <p>I currently use the following VBA code to parse this into a workbook:</p>
  
  <pre><code>myArray = Split(myReply, "[{:#:}]")
  myRow = 1
  For Each element In myArray
      myRow = myRow + 1
      subArray = Split(element, "[|:#:|]")
      myCol = 2
      For Each subelement In subArray
          myCol = myCol + 1
          Cells(myRow, myCol).Value = subelement
      Next subelement
  Next element
  </code></pre>
  
  <p>I am about to start optimising the code in this workbook and I am aware I can do something like (pseudo code):</p>
  
  <pre><code>for each element....
      Range("C2:F2").Value = Split(element, "[|:#:|]") 'Example row number would be incremental
  </code></pre>
  
  <p>However is there a way to do it so that I can split into the entire Range?</p>
  
  <p>For example, If I know there are 29 "rows" within the data that has been returned, I would like to be able to use <code>split</code> to place the data into all the rows. </p>
  
  <p>I imagine the syntax would be something similar to the below, however this doesn't seem to work:</p>
  
  <pre><code>Range("C2:F29").Value = Split(Split(element, "[|:#:|]"),"[{:#:}]")
  </code></pre>