VBA Problem with Formulas-to-Values in Non-Adjacent Cells

[pbornemeier]

This code exposes what I consider to be a bug in Excel along with a workaround. It will probably not occur in most circumstances.

Option Explicit

Sub DemoProblemWhenUpdatingFormulasInNonAdjacentCells()

    'This code demonstrates a problem that I found the hard way
    '  as well as a solution.  Set VBE small enough to see
    '  worksheet as well as code.  Step through code and watch what happens.
    
    Dim rng As Range
    
    'Reset Demo Data Field
    With Range("A1:H20")
        .FormulaR1C1 = "=Row()"
        .Value = .Value
        .Interior.Color = -4142
    End With
    Stop
    'Update Values in a block of cells
    With Range("C5:F10")
        .FormulaR1C1 = "=R[-1]C*5"  'The formulas show correct answers
        .Interior.Color = vbYellow
        Stop
        .Value = .Value 'The values show the correct answer.
        Stop
    End With
    
    'Reset Demo Data Field
    With Range("A1:H20")
        .FormulaR1C1 = "=Row()"
        .Value = .Value
        .Interior.Color = -4142
    End With
    Stop
    'Update Values in a few separate randomly placed cells
    With Range("G15,D9,D18,H20,H7,B5")
        .FormulaR1C1 = "=R[-1]C*5"  'The formulas show correct answers
        .Interior.Color = vbYellow
        Stop
        .Value = .Value 'The values show the answer for
                        'the first cell in the range.  This behavior was
                        'not what I expected.  Explore further.
    End With
    
    'Reset Demo Data Field
    With Range("A1:H20")
        .FormulaR1C1 = "=Row()"
        .Value = .Value
        .Interior.Color = -4142
    End With
    Stop
    'Update Values in two separate blocks of cells, and a slightly different formula
    With Range("C3:E7,E12:F14") 'First block is larger
        .FormulaR1C1 = "=R[-1]C*(5+COLUMN())"  'The formulas show correct answers
        .Interior.Color = vbYellow
        Stop
        .Value = .Value     'The values in the second block of cells are filled
                            '(columns then rows) with the values in the first block
                            'of cells (columns then rows)
    End With
    
    'Reset Demo Data Field
    With Range("A1:H20")
        .FormulaR1C1 = "=Row()"
        .Value = .Value
        .Interior.Color = -4142
    End With
    Stop
    'Update Values in two separate blocks of cells, and a slightly different formula
    With Range("E12:F14,C3:E7") 'Second block is larger
        .FormulaR1C1 = "=R[-1]C*(5+COLUMN())"  'The formulas show correct answers
        .Interior.Color = vbYellow
        Stop
        .Value = .Value     'The values in the second block of cells are filled
                            '(columns then rows) with the values in the first block
                            'of cells (columns then rows).  If there are fewer
                            'cells in the first block, the remaining cells in the
                            'second block are now #N/A
        Stop
    End With
    
    
    'SOLUTION
    'Update formula by areas to preserve the correct answers in each area
    
    'Reset Demo Data Field
    With Range("A1:H20")
        .FormulaR1C1 = "=Row()"
        .Value = .Value
        .Interior.Color = -4142
    End With
    Stop
    'With two separate blocks of cells, and a slightly different formula
    With Range("E12:F14,C3:E7")
        For Each rng In .Areas
            With rng
                .FormulaR1C1 = "=R[-1]C*(5+COLUMN())"  'The formulas show correct answers
                .Interior.Color = vbYellow
                Stop
                .Value = .Value 'The values show correct answers
                Stop
            End With
        Next
    End With

    'The solution works in all instances that I tested.  The requirement to do this does
    '  look like an Excel bug to me since the formulas do show the correct values and
    '  errors come to view when converting them to values.
    
End Sub

 

[JoeMo]

This simplified code essentially reproduces (Test1) some of what you have observed. Working with a range that has more than one area can produce surprises that are not necessarily indicative of a bug.

Sub test1()
'Place random numbers (as constants) in several non-contiguous cells then select those cells
With Selection
.Value = .Value ' Places the value of the upper-left cell in all selected cells
End With
End Sub
Sub test2()
'Place random numbers (as constants) in several non-contiguous cells then select those cells
For Each c In Selection
    c.Value = c.Value 'Each cell in the selection gets correct value
Next c
End Sub