building a formula with non-US country setting

[Ward Germonpré]

Hi,


The code below works for Integer cel.values, but not for broken values.

I think it has something to do with countrysettings, in Belgium we use
comma's to represent broken numbers.

In the debugger I see that VBA builds the formula like this:
=Int($i$1 * 3.2)
but Excel using my countrysettings expects this
=Int($i$1 * 3,2)
and gives a runtime error.


Public Sub gewicht()
Dim cel As Range, rng As Range

With ActiveSheet
Set rng = .Range(.Cells(2, 5), .Cells(33000, 5))
i = 2
For Each cel In rng
If cel.Value <> "" Then
cel.Offset(0, 5).Formula = "=int($i$1 * " & cel.Value & ")"

End If
i = i + 1
Next
End With
End Sub


Any help appreciated

Ward

 

[Ward Germonpré]

Hi,


The code below works for Integer cel.values, but not for broken values.

I think it has something to do with countrysettings, in Belgium we use
comma's to represent broken numbers.

In the debugger I see that VBA builds the formula like this:
=Int($i$1 * 3.2)
but Excel using my countrysettings expects this
=Int($i$1 * 3,2)
and gives a runtime error.


Public Sub gewicht()
Dim cel As Range, rng As Range

With ActiveSheet
Set rng = .Range(.Cells(2, 5), .Cells(33000, 5))
i = 2
For Each cel In rng
If cel.Value <> "" Then
cel.Offset(0, 5).Formula = "=int($i$1 * " & cel.Value & ")"

End If
i = i + 1
Next
End With
End Sub


Any help appreciated

Ward

Solved it :

cel.Offset(0, 5).FormulaLocal = "=INTEGER($i$1 * " & cel.Value & ")"



thx

Ward