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

 

[steve the fish]

Id be surprised if its the code. Try it on a single cell. Does that change? Are there hidden rows? Are the initial formulas really the same as yours?

 

[Skovgaard]

Id be surprised if its the code. Try it on a single cell. Does that change? Are there hidden rows? Are the initial formulas really the same as yours?

We have both tested on the same example and with two different results. So no hidden rows, the formulas are exactly the same, as I wrote them. So I also think, that this is very strange.
Can there be a setting somewhere, that can influence on this?

 

[gravanoc]

Hi Skovgaard,

Is it just 1 workbook open?
If yes, and it is a setting of some sort, one quick way to check is to create a bunch of Debug.Print Application.XXSetting to see what turns up True ur False.

When I do this, I copy straight from the VBA Ref under Application and paste to match destination in Excel, then create a column of Debug.Print then a column of "Application." (w/o quotes), then concat the 3 columns in correct order, copy and paste values, then copy into VBE. If done correctly it only takes about 1 min.

Any Application property that isn't Bool can simply be excised or commented out if the sub hits a snag.

Compare the results from both CPUs.

 

[gravanoc]

Oh, one other possibility is that one of you is running an Insider edition. Maybe you could share some screenshots so I can see the layouts?

 

[Skovgaard]

Hi Skovgaard,

Is it just 1 workbook open?
If yes, and it is a setting of some sort, one quick way to check is to create a bunch of Debug.Print Application.XXSetting to see what turns up True ur False.

When I do this, I copy straight from the VBA Ref under Application and paste to match destination in Excel, then create a column of Debug.Print then a column of "Application." (w/o quotes), then concat the 3 columns in correct order, copy and paste values, then copy into VBE. If done correctly it only takes about 1 min.

Any Application property that isn't Bool can simply be excised or commented out if the sub hits a snag.

Compare the results from both CPUs.

Hi Gravanoc,

The Macros are being stored in another workbook, so 2 workbooks are open at the same time. The workbook with the macros are hidden and open automatically, when Excel is opened.
But this is the same on both CPUs.

I'm uncertain how to create a bunch of Debug.Print. And the two CPUs are not on same location

/Ulrik

 

[steve the fish]

How can you be certain what 'Selection' is when its run then?

 

[Skovgaard]

How can you be certain what 'Selection' is when its run then?

Because we have tested the same spreadsheet, and when I run the macro, it doesn't matter whether I select one cell, two cells or all six cells, It works as it should.

/Skovgaard

 

[steve the fish]

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

 

[gravanoc]

If you are still having problems, you could upload your workbook and I will see if the issue occurs on my cpu.