VB Assistance - Looping Process

[PeteB1]

Hello.
I have a worksheet that will contain data in sheet A . Column A. Range can be anywhere from A2:A1000
Trying to figure out how to load 50 rows of data at a time, then send to the software (url) that I am using, etc..
The Process currently works but only up until 50 rows of data as that's a limitation of the software itself.
So basically there are 3 steps, load data from excel, upload data to an external url, and get data from an external url.

I am using row count to get the number of rows of data.
I need to loop the process to go from cell A2 (since A1 is the header info) and go down until the end of the data (if less than 50 lines).
If more than 50 lines, stop ... perform the load and retrieve piece (that is currently working with the load file) then repeat as needed (another 50 rows of data) until the last row. So if data is 120 lines, I need to do 3 loads (50, next 50, then the remaining 20)
Current code and thoughts'get row count
X =WorksheetFunction.CountA(Sheets("A").Range("A2:A1000"))
'STEP 1 - create load file to send to external URLWhile i <= x ​

Set rng =Sheets("A").Range("A2:A1000")
myitem("field1") = cell.Value
myitem("field2") = cell.Offset(0,2).Value
myitem("field3") = cell.Offset(0, 3).Value
myitem("field4") = cell.Offset(0, 4).Value
(fields 1 -4 will remain the same throughout the file)
i = 0
For Each cell In rng

myitem("field5") = cell.Offset(0, 1).Value
(field 5 is the only one that has different data in it - this is the field that needs to loop until it gets to the end of the data or 50 lines of data . whichever comes first.... if greater than 50 .... grab 50 rows at a time...loop...etc...)
items.Add myitem

Set myitem = Nothing

Set subitem = Nothing

i = i + 1


Next
'Step2 - (send portion of the macro is working)
'Step 3 - (retrieve portion of the macro is working)

Not sure if I have to clear the load file once steps 1-2-3 are complete for 50 rows of data
​

 

[onlyadrafter]

Hello,

not thoroughly tested and created in a bit of a rush.

X = Range("A" & Rows.Count).End(xlUp).Row
i = 2
For MY_LOOPS = 1 To Int(X / 50) + 1
'STEP 1 - create load file to send to external URLWhile i <= x
Set Rng = Sheets("A").Range("A" & i & ":A" & i + 49)
myitem("field1") = cell.Value
myitem("field2") = cell.Offset(0, 2).Value
myitem("field3") = cell.Offset(0, 3).Value
myitem("field4") = cell.Offset(0, 4).Value
'(fields 1 -4 will remain the same throughout the file)
For Each cell In Rng

myitem("field5") = cell.Offset(0, 1).Value
'(field 5 is the only one that has different data in it - this is the field that needs to loop until it gets to the end of the data or 50 lines of data . whichever comes first.... if greater than 50 .... grab 50 rows at a time...loop...etc...)
'items.Add myitem

Set myitem = Nothing

Set subitem = Nothing

i = i + 1


Next
Next MY_LOOPS

is this anywhere close to your requirements?

 

[PeteB1]

Hello and thank you for the feedback. I believe so. I am just soo new at VB.
Put another way....maybe this will explain it a bit clearer.​

GETDATA:
sheet = a
cell a1 header (text) = field1
cell b1 header (text) = field2
cell c1 header (text) = field3
cell d1 header (text) = field4
cell e1 header (text) = field5
data range could be a2 and that’s it or as much as a2:a1000
loop until the end or max up 50 lines of data at a time
using
myitem("field1") = cell.Value
myitem("field2") = cell.Offset(0, 1).Value
myitem("field3") = cell.Offset(0, 2).Value
myitem("field4") = cell.Offset(0, 3).Value
myitem("field5") = cell.Offset(0, 4).Value
items.Add myitem
Set myitem = Nothing
Set subitem = Nothing
Again loop until the end of the data set but the GETDATA and OUTPUT can only be a max of 50 at a time
Then goto OUTPUT:
I have the logic to send and receive from URL working successfully
OUTPUT:
Logic to take the 50 rows of data or end of dataset (If lessthan 50) and put it in (starting in cell F2)
If dataset is greater > 50 then loop back to GETDATA (starton the 51^st line of data and continue for another 50 rows) then OUTPUT…repeat as needed until end of dataset.
Some of my outputs can be ..lets say 820 rows of data.
So the loop process for GETDATA and OUTPUT will need to happen…16 times for the full 50 at a time and then a 17^th time for the remaining20 records
For Each Item In output file that needs to be extracted in cellF2, etc..
Maybe j is a variable?
Sheets("A").Cells(j, 6).Value = Item("outputfield1")
Sheets("A").Cells(j, 7).Value = Item("outputfield2")
Sheets("A").Cells(j, 8).Value = Item("outputfield3")
Sheets("A").Cells(j, 9).Value = Item("outputfield4")
Sheets("A").Cells(j, 10).Value = Item("outputfield5")
Sheets("A").Cells(j, 11).Value = Item("outputfield6")
Sheets("A").Cells(j, 12).Value = Item("outputfield7")
​

j = j + 1 ?​