Run Time Error 13 (Type mismatch) due to different regional settings

Hi,

I am in the UK and I write reports for colleagues in other offices around the world. I wrote this code with a simple do while loop whic adds a calculation to the end of each row where the first cell is not blank. The calculation is:

PHP Code: 

ActiveCell.Offset(0, 10) * ActiveCell.Offset(0, 9) 


Simple, right?... no! With my English regional settings it would show the calculation as this:

PHP Code: 

.0764000000000000 * 1,000 


and that works fine for me and my US colleagues but not for my friends in France, because theirs would be

PHP Code: 

,0764000000000000 * 1.000 


even if not for the , at the start of the first number, it would multiply by 1 instead of 1,000 so would give completely the wrong answer!

Obviously I can't expect all of my French colleagues to change their regional settings just so that they can run this report, so how can I fix this issue?

Thanks,

Soph

Hi !

Whatever the decimal separator :

PHP Code: 

ActiveCell.Offset(0, 10).Value * ActiveCell.Offset(0, 9).Value 


Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

OK, thanks for your replies. The latest problem I have is that because the rate I am trying to multiply by is a text field in an OLAP cube, it is always shown as
.0764000000000000. So I added:

PHP Code: 

        If Application.International(xlDecimalSeparator) = "," Then
                Rate = "0" & Replace(ActiveCell.Offset(0, 9), ".", ",")
            Else
                Rate = ActiveCell.Offset(0, 9)
        End If 


When I run the code (I changed my regional setting to French to test), the calculation doesn't work and just returns zeros for everything! I think this is because the first bit makes the rate a text string but I can't work out how to change it to number. I tried Rate.NumberFormat = "General" but I got invalid qualifier error, how do I do it or is there a beter way than what I've done?

Thanks again