building a formula with non-US country setting

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é" <kingalbertII@forpresident.com> wrote in
news:Xns97D264AD19A74kingalbertIIforpresi@195.130.132.70:

> 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