building a formula with non-US country setting

W

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
 
W

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top