Macro to convert data

[unwantedoz]

Hello, I need help making a macro. I spent an hour or two couple of hours googling how to make macros but I still have no idea how to do what I want to.

Anyway I want display a chart on a website. I will be using fusioncharts.js to make it as trying it I have found it really easy to use.

My problem is the data is not in the format it needs. It needs it in either JSON or XML as a single string format.

Here is an example workbook with the data manually change to how it needs to look bellow it.

So if the data was

Column 1_______Column 2
25/08/2015_____112

I need the two pieces of data in that entry (the 25/08/2015 and the 112) converted to (doesn't matter where on the page it is or if a new sheet etc) an entry which takes up 1 Column & 4 rows being

Column X
{
"label": "23/08/2015",
"value": "78"
},

or into a single cell (including the < and > at the start and end)
<set label='23/08/2015' value='78' />

For the XML type it doesn't matter if each entry is in a separate cell (next to each other, they can not be under each other) or if they are all added together and put into the one cell.

 

[chirayu]

It counts the number of rows in Column A to figure out how many it needs to put together. So when you're running it, make sure your you remove anything after row 12 from your sample file cos its just examples u put of your data

Sub JSON()

Dim Val As String

Val = "{" & Chr(10) & """" & "label" & """" & ":" & """" & Format(Cells(2, 1), "DD/MM/YYYY") & """" & "," & Chr(10) & """" & "value" & """" & ":" & """" & Cells(2, 2) & """" & Chr(10) & "},"

For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row

  Val = Val & Chr(10) & "{" & Chr(10) & """" & "label" & """" & ":" & """" & Format(Cells(i, 1), "DD/MM/YYYY") & """" & "," & Chr(10) & """" & "value" & """" & ":" & """" & Cells(i, 2) & """" & Chr(10) & "},"

Next

Val = Left(Val, Len(Val) - 1)
Range("E1") = Val

End Sub

Sub XML()

Dim val As String

val = "<set label='" & Format(Cells(2, 1), "DD/MM/YYYY") & "' value='" & Cells(2, 2) & "' />"

For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row

  val = val & "<set label='" & Format(Cells(i, 1), "DD/MM/YYYY") & "' value='" & Cells(i, 2) & "' />"

Next

Range("E1") = val

End Sub

 

[unwantedoz]

Thank you Chirayu those two macro work great