I am trying to parse JSON object but I encountered a problem with numeric value. The json example and its parsing is in my previous question here.
[{"Id":"2604","Price": 520.25, "State": "true"},{"Id":"2605","Price": 322.15, "State": "false"}]
I need to replace a dot with comma in "Price" item:
ws.Cells(currentRow, startColumn + 1).Value = Replace(jsonRow("Price"), ".", ",")
The price is in a numeric format but I need to replace "." with "," but it apparently works only for strings which it is not the case here.
"Price": 520.25
And I need it to be changed to "520,25" before it is inserted to the cell. It should stay numeric type but the comma is necessary.
Full Code:
Sub Test()
Dim jsonText As String
Dim jsonObj As Dictionary
Dim jsonRows As Variant
Dim jsonRow As Variant
Dim ws As Worksheet
Dim currentRow As Long
Dim startColumn As Long
Dim i As Long
Set ws = Worksheets("VIEW")
'Create a real JSON object
jsonText = ws.Range("A1").Value
'Parse it
Set jsonRows = JSON.parse(jsonText)
'Set the starting row where to put the values
currentRow = 1
'First column where to put the values
startColumn = 2 'B
'Loop through all the values received
For Each jsonRow In jsonRows
'Now set all the values in this row
ws.Cells(currentRow, startColumn).Value = jsonRow("Id")
ws.Cells(currentRow, startColumn + 1).Value = Replace(CStr(jsonRow("Price")), ".", ",")
ws.Cells(currentRow, startColumn + 2).Value = jsonRow("State")
'Increment the row to the next one
currentRow = currentRow + 1
Next jsonRow
End Sub
Replace(CStr( jsonRow("Price") ), ".", ",")If that doesn't work then it would be helpful to update your question with the exact error message you're getting (if any)