vba - Show error message if amount entered not follow requested format

Question

I have a range of amounts in column "K" in Excel. I would like the macro to go cell by cell starting in row 9 and show an error message if the amount in the cell is not in format ####,##.

So for example, I would expect an error message to appear if the amount is "1234.56" but not if the amount entered is "9,34"

I have below code but somehow it does not work. What should I write differently?

Sub Macro1()

lastrowK = Sheet1.Range("K" & Rows.Count).End(xlUp).Row
Col = "K"

For i = 9 To lastrowK
    If InStr(1, Cells(i, Col), ".", vbTextCompare) <> 0 Then
        MsgBox "Please check amount in following Cell " & Worksheets("Sheet1").Cells(i, Col).Address & "Format should be ####,##"

    End If
Next i
End Sub

i would check whether data validation rules can be used to cover this — QHarr, Dec 03 '17 at 13:54
You may want to look here [Application.ThousandsSeparator/Application.DecimalSeparator](https://stackoverflow.com/questions/27905412/vba-double-as-string-with-comma-and-not-point) to handle Cstr for your locale changing the "." to a "," when using a CStr conversion. — QHarr, Dec 03 '17 at 14:41
Thanks! With the Application.Thousand helped to achieve what I wanted. — Jose Manuel, Dec 03 '17 at 15:12

score 0 · Answer 1 · answered Dec 03 '17 at 15:13

See below working code:

Sub Macro1()

   lastrowK = Sheet1.Range("K" & Rows.Count).End(xlUp).Row
   Col = "K"

   For i = 2 To lastrowK
       If (Application.ThousandsSeparator = ".") Then
            Cells(i, Col).Value = Replace(Cells(i, Col).Value, ".", "")
        End If
        If (Application.DecimalSeparator = ".") Then
             Cells(i, Col) = Replace(Cells(i, Col).Value, ".", ",")
        End If
   Next i
   End Sub

vba - Show error message if amount entered not follow requested format

1 Answers1