title: Update array based on values of another array
body: <p>Trying to create a subroutine where a user copies and paste a recipe into a cell, and the allergens become automatically capitalised...e.g. (Milk becomes MILK). My SA2 array just holds blank values?</p>
  <p>PS I know I can use replace / find argument, I want to do it through an array just for personal development.</p>
  <pre><code>Sub Allergen()
  
  Dim nr As Integer
  Dim r As Range
  Dim sa As Variant
  Dim sa2 As Variant
  Dim i As Integer
  
  
  nr = WorksheetFunction.CountA(Columns(&quot;E:E&quot;))
  sa = Split(Sheets(&quot;Sheet1&quot;).Range(&quot;E&quot; &amp; nr).Offset(-1, 0))
  sa2 = Split(Sheets(&quot;Sheet1&quot;).Range(&quot;E&quot; &amp; nr).Offset(-1, 0))
  
  ReDim Preserve sa2(0 To UBound(sa))
  
  For i = LBound(sa) To UBound(sa)
  
  If sa(i) = &quot;Milk&quot; Then
  sa2(i) = &quot;MILK&quot;
  End If
  </code></pre>