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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]=SUM(A1:A5)
[/TD]
[TD]=SUM(A1:B5)
[/TD]
[/TR]
[TR]
[TD]=SUM(A2:A6)
[/TD]
[TD]=SUM(A2:B6)
[/TD]
[/TR]
[TR]
[TD]=SUM(A3:A7)
[/TD]
[TD]=SUM(A3:B7)
[/TD]
[/TR]
</tbody>[/TABLE]

Correct result of macro (My PC)
[TABLE="width: 500"]
<tbody>[TR]
[TD]=SUM($A1:$A5)
[/TD]
[TD]=SUM($A1:$B5)
[/TD]
[/TR]
[TR]
[TD]=SUM($A2:$A6)
[/TD]
[TD]=SUM($A2:$B6)
[/TD]
[/TR]
[TR]
[TD]=SUM($A3:$A7)
[/TD]
[TD]=SUM($A3:$B7)
[/TD]
[/TR]
</tbody>[/TABLE]

Wrong result of macro (another PC)
[TABLE="width: 500"]
<tbody>[TR]
[TD]=SUM($A1:$A5)
[/TD]
[TD]=SUM($A1:$B5)
[/TD]
[/TR]
[TR]
[TD]=SUM($A3:$A7)
[/TD]
[TD]=SUM($A3:$B7)
[/TD]
[/TR]
[TR]
[TD]=SUM($A5:$A9)
[/TD]
[TD]=SUM($A5:$B9)
[/TD]
[/TR]
</tbody>[/TABLE]

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