0

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
6
  • 1
    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) Commented Nov 7, 2016 at 21:31
  • Sorry, that doesn't work either. The error message is: Run-time error '9': Subscript out of range Commented Nov 7, 2016 at 22:21
  • It would help to update your question with the full code you're using. Commented Nov 7, 2016 at 23:20
  • Your code works for me with no errors. Commented Nov 8, 2016 at 1:50
  • I don't have an english version of Excel, so "520.25" is not considered a number. Instead of there has to be commas to express the decimal point. Maybe that is the problem. Commented Nov 8, 2016 at 2:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.