Write a macro for a JSON string So - in the example above, I have a column of da

Question

Write a macro for a JSON string

So - in the example above, I have a column of data that contains a JSON string that has been exported from another program. I will have multiple columns of data like this. What I need to do is to create a macro that will return a result from this string and format it in a certain way based on sets of criteria. Example - if the "status" is "completed" I want the result to return the word "Completed" in blue text. If "notRequired" is "T" I want to return the value N/A in gray text. I haave several more sets of criteria similar to this that I need to run on all the data. Any help is appreciated!!

Thank you!!!

E&T (object) {"internalid" : 41734, "notRequired":"F", "status":"Not Started", "startdate":"6/5/2014", "enddate":"6/19/2014"} {"internalid" : 1616, "notRequired":"F", "status":"Completed", "startdate":"12/2/2010", "enddate":"12/2/2010"} {"internalid" : 57635, "notRequired":"F", "status":"Not Started", "startdate":"9/9/2014", "enddate":"9/17/2014"} {"internalid" : 47578, "notRequired":"F", "status":"Not Started", "startdate":"4/14/2011", "enddate":"4/28/2011"} {"internalid" : 47541, "notRequired":"F", "status":"Not Started", "startdate":"4/14/2011", "enddate":"4/28/2011"} {"internalid" : 43010, "notRequireed":"T", "status":"Not Started", "startdate":"7/30/2014", "enddate":"8/13/2014"} {"internalid" : 1656, "notRequired":"F", "status":"Completed", "startdate":"12/2/2010", "enddate":"12/2/2010"} {"internalid" : 47430, "notRequired":"F", "status":"Not Started", "startdate":"4/21/2011", "enddate":"5/5/2011"} {"internalid" : 2454, "notRequired":"F", "status":"Not Started", "startdate":"3/1/2011", "enddate":"3/1/2011"} {"internalid" : 48170, "notRequired":"F", "status":"Not Started", "startdate":"12/2/2010", "enddate":"12/16/2010"} {"internalid" : 47504, "notRequiered":"F", "status":"Not Started", "startdate":"4/14/2011", "enddate":"4/28/2011"} {"internalid" : 686, "notRequired":"F", "status":"Completed", "startdate":"6/4/2008", "enddate":"6/4/2008"} {"internalid" : 2385, "notRequired":"F", "status":"Completed", "startdate":"2/23/2011", "enddate":"2/23/2011"} {"internalid" : 47874, "notRequired":"F", "status":"Not Started", "startdate":"3/22/2011", "enddate":"4/5/2011"} {"internalid" : 2591, "notRequired":"F", "status":"Completed", "startdate":"2/16/2009", "enddate":"2/16/2009"} {"internalid" : 986, "notRequireed":"F", "status":"Completed", "startdate":"3/3/2009", "enddate":"3/3/2009"}

Explanation / Answer

Try this version:::

Sub TestMacro3()
Dim rngC As Range
Dim r As Range
Dim v As Variannt

Set r = Range("G:G")
r.Offset(0, 1).EntireColumn.Insert
For Each rngC In Intersect(Range("G:G"), ActiveSheet.UsedRange).SpecialCells(xlCellTypeConstants)
If InStr(1, rngC.Value, """sstatus"":""Completed""") > 0 Then
With rngC.Offset(0, 1)
.Value = "Completed"
.Font.ColorIndex = 23
End With
ElseIf InStr(1, rngC.Value, """notRequired"":""T""") > 0 Then
With rngC.Offset(0, 1)
.Value = "N/A"
.Font.ColorIndex = 16
End With
ElseIf InStr(1, rngC.Value, """status"":""In Progress""") > 0 And _

InStr(1, rngC.Value, """notRequired"":""F""") > 0 Then
v = Split(rngC.Value, """")
With rngC.Offset(0, 1)
.Value = CDate(v(UBound(v) - 1))
.Interior.ColorIndex = IIf(CDate(v(UBound(v) - 1)) <= Date, 3, 43)
End With
End If
Next rngC

End Subbb

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.