Put information in multiple ranges

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
I am using the below code to put data acquired from an API using a registration number and then put responses in a table (Table8). If a criteria is met as well as putting the data into Table8, I also want to put the registration number and a date/time stamp into "Table10". Is it possible to reference Table10 as well as Table8 in the 'for' loop?

Thanks in advance.

VBA Code:
    For r = 1 To rngColVRM.Rows.Count
        
        With httpReq
            .Open "GET", registrationEndpointURL & Replace(rngColVRM.Cells(r).Value, " ", ""), False
            .setRequestHeader "Accept", "application/json+v6"
            .setRequestHeader "x-api-key", APIkey
            .send
'            Debug.Print .Status, .StatusText
'            Debug.Print .responseText
            Set JSON = JsonConverter.ParseJson(.responseText)
                                
            If .Status = 200 Then
                If JSON(1).Exists("motTests") Then
                    completedDateValue = JSON(1)("motTests")(1)("completedDate")
                    completedDate = DateSerial(Mid(completedDateValue, 1, 4), Mid(completedDateValue, 6, 2), Mid(completedDateValue, 9, 2)) 'date only
                    testResult = JSON(1)("motTests")(1)("testResult")
                    odometerValue = JSON(1)("motTests")(1)("odometerValue")
                    make = JSON(1)("make")
                    Model = JSON(1)("model")
                    primaryColour = JSON(1)("primaryColour")

                    If rngColComplete.Cells(r) <> "Yes" Then
                        rngColLastMOT.Cells(r).Value = completedDate
                        rngColResult.Cells(r).Value = testResult
                        rngColMOTOdo.Cells(r).Value = odometerValue
                        rngDateChecked.Cells(r).Value = Date
                        If completedDate >= rngColDate.Cells(r) And rngColRemoval.Cells(r) = "MOT" And testResult = "PASSED" Then
                            rngColStatus.Cells(r) = "Auto closed - System generated"
                            rngColStatusDate.Cells(r) = Date
                            rngColStatusOff.Cells(r) = UserName & " - System generated"
                            rngColComplete.Cells(r) = "Yes"
'                            rngRemVRM = rngColVRM      '<<<<< rngRemVRM is in Table10 - rngColVRM is the one referenced in this for loop
'                            rngRemDate = Now           '<<<<< rngRemDate is in Table10
                        End If
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,127,588
Messages
5,625,668
Members
416,125
Latest member
NeedExcelHelp2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top