title: How To Have VBA Insert Formula Result as a Value
body: <p><a href="https://stackoverflow.com/questions/47231508/array-range-and-isempty-if-then-statement-vba-overwrites-everything-instead-of">I got help last week</a> getting my syntax and ranges correct, and thought I could just do a <code>vlookup</code> to finish it but apparently I was mistaken.  It just seems like when I try to research how to accomplish this, I find various examples but I don't have the background to translate it to my code.  </p>
  
  <p>The macro runs and does <em>almost</em> everything its supposed to do.  But in addition to inserting the arrays, there are 3 other cells that need values when there are blank cells in my ‘sourcerng’. </p>
  
  <p><img src="https://i.stack.imgur.com/4dihp.jpg" alt="Snip of my worksheet"></p>
  
  <p>This is the logic for the cells that need values (the values are already in my worksheet, I just need to get them to these blank cells).  I tried to do an IIF statement for these but I still have no idea what I'm doing.  Would that be the best way? Should it just be another IF THEN statement?  </p>
  
  <p><strong>rngBE</strong>   - IF Column Z = 0 Then copy value from corresponding row in column O.  Otherwise copy value from column Z</p>
  
  <p><strong>rngBG</strong>   - IF Column AA = "Unknown" Then copy value from corresponding row in column I.  Otherwise copy value from column AA.</p>
  
  <p><strong>rngBK</strong>   - IF Column AB = "Unknown" Then copy value from corresponding row in column N.  Otherwise copy value from column AB.</p>
  
  <pre><code>Sub AutomateAllTheThings6()
  Dim arr3() As String
  Dim arr11() As String
  'Dim resBE As String
  Dim rng3 As Range
  Dim rng11 As Range
  Dim rngBE As Range
  Dim rngBG As Range
  Dim rngBK As Range
  Dim sourcerng As Range
  'Dim firstRow As Long
  Dim lastRow As Long
  'Dim i As Long
  
      Call OptimizeCode_Begin
          'firstRow = 2
          lastRow = ActiveSheet.Range("D1").End(xlDown).Row
          Set rng3 = ActiveSheet.Range("BH2:BJ" &amp; lastRow)
          Set rng11 = ActiveSheet.Range("BL2:BV" &amp; lastRow)
          Set rngBE = ActiveSheet.Range("BE2:BE" &amp; lastRow)
          Set rngBG = ActiveSheet.Range("BG2:BG" &amp; lastRow)
          Set rngBK = ActiveSheet.Range("BK2:BK" &amp; lastRow)
          Set sourcerng = ActiveSheet.Range("BE2:BE" &amp; lastRow)
          arr3() = Split("UNKNOWN,UNKNOWN,UNKNOWN", ",")
          arr11() = Split("UNKNOWN,UNKNOWN,UNKNOWN,UNKNOWN,UNKNOWN,UNKNOWN,00/00/0000, _
                           00/00/0000,00/00/0000,00/00/0000,NEEDS REVIEW", ",")
              For Each cell In sourcerng
                  If IsEmpty(cell) Then
                      Intersect(rng3, ActiveSheet.Rows(cell.Row)).Value = arr3
                      Intersect(rng11, ActiveSheet.Rows(cell.Row)).Value = arr11
                      '***PLS HELP***
                      Intersect(rngBE, ActiveSheet.Rows(cell.Row)).Value = "WEEEEE"
                      Intersect(rngBG, ActiveSheet.Rows(cell.Row)).Value = "WOOOOO"
                      Intersect(rngBK, ActiveSheet.Rows(cell.Row)).Value = "WAAAAA"
                  End If
              Next
          Range("BR2:BU2").Select
              Range(Selection, Selection.End(xlDown)).Select
              Selection.NumberFormat = "mm/dd/yyyy"
          Columns("BF:BF").Select
          Selection.Delete Shift:=xlToLeft
      Call OptimizeCode_End
  End Sub
  
  
  '*********TESTING***********
  'resBE      = IIf(Cells(13,Z).Value = 0, Cells(13,BE).Value = Cells(13,Z), Cells(13,BE).Value = Cells(13,O)) 
  
  '***************************************
  'For i = firstRow To lastRow
  '           valZ = Range("Z" &amp; i)
  '           valOh = Range("O" &amp; i)
  '           
  '           If valZ = 0 Then
  '               rngBE.Value = valOh
  '               Else rngBE.Value = valZ
  '           End If
  </code></pre>