Application.ConvertFormula behaves differently

[Skovgaard]

Hi All,

I have below macro, which works on my PC. On another PC, the same code behaves differently.

Any ideas why this is different, we both run Excel 2016?

Sub setFixedCol()
'Hotkey: Ctrl + Shift + U
Dim c As Range
For Each c In Selection
    If c.HasFormula Then
        c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, 3)
    End If
Next
End Sub

Cells before run of macro:

=SUM(A1:A5)	=SUM(A1:B5)
=SUM(A2:A6)	=SUM(A2:B6)
=SUM(A3:A7)	=SUM(A3:B7)

<tbody>
</tbody>

Correct result of macro (My PC)

=SUM($A1:$A5)	=SUM($A1:$B5)
=SUM($A2:$A6)	=SUM($A2:$B6)
=SUM($A3:$A7)	=SUM($A3:$B7)

<tbody>
</tbody>

Wrong result of macro (another PC)

=SUM($A1:$A5)	=SUM($A1:$B5)
=SUM($A3:$A7)	=SUM($A3:$B7)
=SUM($A5:$A9)	=SUM($A5:$B9)

<tbody>
</tbody>

As you can see, on the second PC, it jumps a row...!?

/Skovgaard

 

[Skovgaard]

This is the same without the loop. Whats that do?

On Error Resume Next
With Selection.SpecialCells(xlCellTypeFormulas)
    .Formula = Application.ConvertFormula(.Formula, xlA1, xlA1, 3)
End With

Thanks steve the fish. Your code is tested on both works on both CPUs.

/Skovgaard