title: Loading data range or string from excel file to an array then split in array
body: <p>Is there someone can help me? I have here code that can duplicate entire row to have 2 rows. After duplicating the first entire row , I want to load string from range "G"  into array so that I can get certain string that Am planning to insert in "Thickness" and "width" column for me to use to calculate the "Weight" of the "Profile Type". If you will see I have an array in the code .But that array work differently for me and I had a hard time fulfilling the requirements I need.  The array in my code split the String using "X" as delimiter . Once the string was split it will add another cells for each split string. what I want is to do the split not in the column but in the array only so that I can maintain the data in G . I will use the string assigned in the array to get "Thickness and Width" of the profile which is "15 as Thickness and 150 as width". If there's any way to do same thing using other code it will be more helpful to simplify the code. 
  Reminder that Profiletype string vary its length . Sometimes profile width are 4 digits (LB1000X4500X12/15)</p>
  
  <p>Below are the snapshot of my worksheet for you to identify what the result will be.</p>
  
  <pre><code>Private Sub CommandButton2_Click()
  
  Dim lastrow As Long
  Dim i As Integer
  Dim icount As Integer
  Dim x As Long
  
      For x = ActiveSheet.UsedRange.Rows.CountLarge To 1 Step -1
  
          If Cells(x, "F") = "LB" Then
          Cells(x, "F") = "ComP"
          Cells(x + 1, "F").EntireRow.Insert
          Cells(x, "F").EntireRow.Copy Cells(x + 1, "F").EntireRow
  
          'array
          'Columns("G:G").NumberFormat = "@"
          Dim c As Long, r As Range, v As Variant, d As Variant
  
      For i = 2 To Range("G" &amp; Rows.Count).End(xlUp).Row '2 to 16 cell
          'v = Split (range("G" &amp; i), "X")
          v = Split((Cells(x, "G") &amp; i), "x")
          c = c + UBound(v) + 1
  
      'Next i
  
  
      For i = 2 To c
  
          If Range("G" &amp; i) &lt;&gt; "" Then
              Set r = Range("G" &amp; i)
              Dim arr As Variant
              arr = Split(r, "X")
              Dim j As Long
              r = arr(0)
  
      For j = 1 To UBound(arr)
  
          Rows(r.Row + j &amp; ":" &amp; r.Row + j).Insert Shift:=xlDown
          r.Offset(j, 0) = arr(j)
          r.Offset(j, -1) = r.Offset(0, -1)
          r.Offset(j, -2) = r.Offset(0, -2)
  
      Next j
  
          End If
      Next i
  End If
  Next x
  
  
  End Sub
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/QAzRu.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QAzRu.jpg" alt="preliminary format"></a></p>
  
  <p><a href="https://i.stack.imgur.com/WgFbN.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WgFbN.jpg" alt="correct result"></a></p>
  
  <p><a href="https://i.stack.imgur.com/UD7bw.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UD7bw.jpg" alt="wrong result"></a></p>
  
  <p><a href="https://i.stack.imgur.com/zEw41.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zEw41.jpg" alt="enter image description here"></a></p>