VBA Runtime Error 1004 when pasting converted json string to another sheet

Question

I have a converted json string response text from a web service call. The code works if I paste it on the same sheet where the button that calls the web service is. But if I try it to put the result on another sheet I am getting "Error 1004 Application Defined or Object Defined error"

The code for the click button on excel is as follows;

Private Sub webcall_Click()
    Dim MyRequest As Object
    Dim JSON As Dictionary
    Dim Header As Range
    Dim Env As String

   Logix = Sheets("Sheet1").Cells(2, "G").Value

    If Env = "" Then
        URL = "https://defaultURL/sqlquery.aspx"
        MsgBox (URL)
    Else
        URL = "https://anotherURL/sqlquery.aspx"
        MsgBox (URL)
    End If

    Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    MyRequest.Open "POST", URL
    MyRequest.Send
    Set JSON = JsonConverter.ParseJson(MyRequest.responseText)

    Dim Values As Variant
    ReDim Values(JSON("chargebackdept table").Count, 3)

    Dim Value As Dictionary
    Dim i As Long

    i = 0
    For Each Value In JSON("chargebackdept table")
        Values(i, 0) = Value("chargebackcategory")
        Values(i, 1) = Value("chargebackdeptid")
        Values(i, 2) = Value("name")
    i = i + 1
    Next Value

    Sheets("Sheet1").Range(Cells(1, "B"), Cells(JSON("chargebackdept table").Count, "D")) = Values
    Sheets("Sheet1").Range("B1").Insert Shift:=xlDown
    Sheets("Sheet1").Range("C1").Insert Shift:=xlDown
    Sheets("Sheet1").Range("D1").Insert Shift:=xlDown
    Set Header = Sheets("Sheet1").Range("B1")
    Header.Value = "ChargeBack_Category"
    Set Header = Sheets("Sheet1").Range("C1")
    Header.Value = "ChargeBack_ID"
    Set Header = Sheets("Sheet1").Range("D1")
    Header.Value = "ChargeBack_Name"
    MsgBox ("Done loading chargeback table")
End Sub

My problem is the line;

Sheets("Sheet1").Range(Cells(1, "B"), Cells(JSON("chargebackdept table").Count, "D")) = Values

If I change it to a different sheet like below it will error out;

Sheets("Sheet2").Range(Cells(1, "B"), Cells(JSON("chargebackdept table").Count, "D")) = Values

related: [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells). — , Jun 16 '17 at 01:14

score 2 · Accepted Answer · 2017-06-16T01:22:19.313

2

Qualify the parent worksheet of the Cells that define the Range.

with workSheets("Sheet1")
    .Range(.Cells(1, "B"), .Cells(JSON("chargebackdept table").Count, "D")) = Values
end with

Note .Cells and not Cells. The . provides the qualifying parent worksheet defined in the With ... End With statement. It could also be,

 workSheets("Sheet1").Range(workSheets("Sheet1").Cells(1, "B"), workSheets("Sheet1").Cells(JSON("chargebackdept table").Count, "D")) = Values

Typically, the 'code for the click button' is on a private worksheet code sheet and not a public module code sheet. In a private worksheet code sheet all unqualified range and cell references default to the worksheet that the code sheet belongs to. You cannot define a range on another worksheet (e.g. sheet2) with the cells from the default worksheet.

edited Jun 16 '17 at 01:22

answered Jun 16 '17 at 01:18

Can I change the activeworksheet to define the range? I am not sure the above answer my problem of pasting my converted json result set to another sheet. Thanks for looking into this. – Dren Jun 16 '17 at 01:27
You should know what worksheet is the destination. Using With ... End With lets you set any worksheet as the destination without penalty. – Jun 16 '17 at 01:29
Finally understood on the second look your solution. Got it working now. Many thanks Jeeped – Dren Jun 16 '17 at 01:33
1

Humbly suggest you find time to go through that [link](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) I left in comments above. If you get through that you will never have this problem again. – Jun 16 '17 at 01:35
It is indeed a very good material to read. Thank you for the reference. – Dren Jun 16 '17 at 01:43

VBA Runtime Error 1004 when pasting converted json string to another sheet

1 Answers1