Macro to reformat numbers imported from ERP system

[zsolt_g]

Hi All,

Unfortunately I am not big in Excel Macros so please bare with me on this one.

When copying information from our ERP system into Excel the values sometimes end up like this: 12456,78- (please note that this is an EU formatting, the comma (,) represents the decimal place).
I have a macro that corrects the number to the following format: -12 457

This was working for me for many years now, but for all of a sudden it somehow broke. If I run it now the number that I end up with is -1 245 678.
This only happens if the minus (-) sign is at the end of the number I run the Macro on. If I have a number like 12345,99 and I run it it transforms it to 12 346 - which is correct.

I reckon Excel must have somehow reset somthing in the formatting options.
I would really appreciate it if you could help! I'll drop the Macro below.

Thank you in advance!

Sub Format_cisel()
   '
   ' Format_cisel Makro
   '
   ' Toto makro prehodí znamienko z konca císla na zaciatok, odstráni bodky a oddelí tisícky
   ' Makro zaznamenané 3.2.2007 , autor sktba
   '
   On Error Resume Next
   'Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
   ReplaceFormat:=False
   
   
   irows = Selection.Rows.Count
   icolumns = Selection.Columns.Count
   For i = 1 To irows
      For j = 1 To icolumns
         If Right(Selection.Cells(i, j), 1) = "-" Then Selection.Cells(i, j) = -Left(Selection.Cells(i, j), Len(Selection.Cells(i, j)) - 1) Else Selection.Cells(i, j) = Selection.Cells(i, j) * 1
      Next j
   Next i
   
   Selection.NumberFormat = "#,##0"
End Sub

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer