title: Why are there inconsistent and broken cell formulas for some rows and not others?
body: <p><strong>Goal: Populate F and G columns with proper formulas depending on total PROD-TIME for a block</strong></p>
  
  <p>This is another issue that has come up after one of my previous questions:
  <a href="https://stackoverflow.com/questions/53049524/how-to-loop-through-blocks-of-rows-in-excel-dataset-instead-of-each-row-indivi">How to loop through &quot;blocks&quot; of rows in excel dataset instead of each row individually (VBA)?</a> </p>
  
  <p>I have been able to loop through blocks of rows and can now get the sum of the PROD-TIME for that particular block. This sum is necessary to determine which formula needs to be used in the F and G columns.</p>
  
  <p>This is best illustrated in this workbook, 
  <a href="https://www.dropbox.com/s/vgnqi00h8xosja3/wip%20Gantt%20Template.xlsx?dl=0" rel="nofollow noreferrer">https://www.dropbox.com/s/vgnqi00h8xosja3/wip%20Gantt%20Template.xlsx?dl=0</a> , where I have shown how I want the formulas to end up in the F and G columns. But for some reason when I run the macro, it just completely breaks. Some of the formulas don't even use reference cells and use the cell value instead, or reference cells don't even appear. Are the blank F and G columns confusing the macro? How can I make sure that every F and G cell gets filled with something? Errors are fine</p>
  
  <pre><code>Sub getStartEndDate()
      Dim WrkSht As Excel.Worksheet
      Dim RngColumn As Range, RngBlock As Range
      Dim totalHrs As Integer 'total PROD-TIME for the given RngBlock
      Dim lastRow As Long
  
      lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
      Set WrkSht = ActiveWorkbook.Worksheets(1)
  
      ' Populate the last row by itself first since the With-statement below needs to reference rows below current row
      Range("E" &amp; lastRow).Formula = "=ROUNDUP(D" &amp; lastRow &amp; "/12,0)"
      Range("G" &amp; lastRow).Value = Range("C" &amp; lastRow).Value
      Range("F" &amp; lastRow).Formula = "=WORKDAY(G" &amp; lastRow &amp; ", -E" &amp; lastRow &amp; ")"
      Columns("F:F").NumberFormat = "yyyy-mm-dd"
  
      With WrkSht
          Set RngColumn = .Range("B2:B" &amp; lastRow)
          'Starts the first block with the first cell in a column.
          Set RngBlock = RngColumn.Cells(1)
  
          'Checks every cell in a column.
          For Each rngcell In RngColumn
              If rngcell.Offset(0,1).Value &lt;&gt; "" Then
                  'Checks whether a cell's value equals the cell below it.
                  If rngcell.Value = rngcell.Offset(1, 0).Value Then
                      'If equal, includes the cell below in the block.
                      Set RngBlock = Union(RngBlock, rngcell.Offset(1, 0))
                  Else
                      'If not equal, that means the block RngBlock ends
                      ' totalHrs is the sum of the "PROD-TIME" for that particular block
                      totalHrs = WorksheetFunction.Sum(Range(CStr(Trim(Chr(64 + RngBlock.Column + 2))) _
                                  &amp; CStr(Trim(Str(RngBlock.Row))) &amp; ":" _
                                  &amp; CStr(Trim(Chr(64 + 2 + RngBlock.Column + RngBlock.Columns.Count - 1))) _
                                  &amp; CStr(Trim(Str(RngBlock.Row + RngBlock.Rows.Count - 1)))))
  
                      If totalHrs &lt; 12 Then
                          ' If total production time (PROD-TIME) is less than 12 hours, then the start and end date should be the same for all rows in that block
                          rngcell.Offset(0, 4).Value = rngcell.Offset(0, 1).Value
                          rngcell.Offset(0, 5).Value = rngcell.Offset(0, 1).Value
                      Else
                          ' If total production time is greater than 12 hours, then start and end dates are based on a different formula 
                          ' e.g. Given row 11, F column formula looks like: =WORKDAY(G11, -E11), G column looks like: =IF(B11=B12,F12,C11) 
                          rngcell.Offset(0, 4).Formula = "=WORKDAY(" &amp; rngcell.Offset(0, 5) &amp; ", -" &amp; rngcell.Offset(0, 3) &amp; ")"
                          rngcell.Offset(0, 5).Formula = "=IF(" &amp; rngcell &amp; "=" &amp; rngcell.Offset(1, 0) &amp; "," &amp; rngcell.Offset(1, 4) &amp; "," &amp; rngcell.Offset(0, 1) &amp; ")"
                      End If
  
                      'Starts the next block with the cell below.
                      Set RngBlock = rngcell.Offset(1, 0)
                  End If
              End If 
  
          Next rngcell
      End With
  End Sub
  </code></pre>