Adding values to excel from an Array

[edTech]

Hello, I am wondering if someone can help me with the following code which is not working correctly. I collect values from an external GUI and add them to an Arraylist. I am trying to add the values to excel in two columns. The results I get is that as it For loops I only get the last value. The amount values into the array will vary between amounts. I also need to add a new row into excel if the last row has a value already in it. Here is my current code:

last_Row = objSheet.Range("B" & objSheet.Rows.Count).End(xlUp).Row

For i=0 to Ubound(ArrayDoc)
If ArrayDoc(i) <> "" or ArrayRev(i) <> "" Then

myArrayList.Add ArrayDoc(i)
myArrayList.Add ArrayRev(I)
End if
next

For each a in myArraylist
objsheet.Range("B2:C" & last_Row) = a

next



Output:

	d	d
	d	d

 

[John_w]

Try this:

    Dim r As Long
    r = 0
    For i = 0 To myArraylist.Count - 1 Step 2
        objSheet.Range("B2:C2").Offset(r).Value = Array(myArraylist(i), myArraylist(i + 1))
        r = r + 1
    Next

Why do you need an ArrayList? A normal array would probably suffice.

 

[edTech]

Try this:

    Dim r As Long
    r = 0
    For i = 0 To myArraylist.Count - 1 Step 2
        objSheet.Range("B2:C2").Offset(r).Value = Array(myArraylist(i), myArraylist(i + 1))
        r = r + 1
    Next

Why do you need an ArrayList? A normal array would probably suffice.

You are correct John, this is what I ended up doing instead of doing the Arraylist based on your advice, but I do not know how to start in Cell 17

For i=0 to Ubound(ArrayDoc)

objSheet.Cells(i,1).Value = ArrayDoc(i)
objSheet.Cells(i,2).Value = ArrayRev(i)



Next

 

[John_w]

What do you mean by cell 17? Row 17 or column 17, or something else?

If you mean row 17, then:

For i=0 to Ubound(ArrayDoc)
  objSheet.Cells(i+17,1).Value = ArrayDoc(i)
  objSheet.Cells(i+17,2).Value = ArrayRev(i)
 Next

 

[edTech]

What do you mean by cell 17? Row 17 or column 17, or something else?

If you mean row 17, then:

For i=0 to Ubound(ArrayDoc)
  objSheet.Cells(i+17,1).Value = ArrayDoc(i)
  objSheet.Cells(i+17,2).Value = ArrayRev(i)
Next

Thank you John_w it worked, but what I did notice is when I add 17 it actually skips two rows and it lands on 19. So I put it for 15 to land on 17.

 

[Peter_SSs]

Seems a bit odd to me to be taking two parallel lists and combining them a row at a time (if relevant) into a single list and then re-dividing them a row at a time into two parallel lists.
Why not skip that middle part and write the relevant components straight to the final destination? Something like

k = 0
For i = 0 To UBound(ArrayDoc)
  If ArrayDoc(i) <> "" Or ArrayRev(i) <> "" Then
    k = k + 1
    objSheet.Cells(17 + k, 1).Value = ArrayDoc(i)
    objSheet.Cells(17 + k, 2).Value = ArrayRev(i)
  End If
Next i

 

[edTech]

Seems a bit odd to me to be taking two parallel lists and combining them a row at a time (if relevant) into a single list and then re-dividing them a row at a time into two parallel lists.
Why not skip that middle part and write the relevant components straight to the final destination? Something like

k = 0
For i = 0 To UBound(ArrayDoc)
  If ArrayDoc(i) <> "" Or ArrayRev(i) <> "" Then
    k = k + 1
    objSheet.Cells(17 + k, 1).Value = ArrayDoc(i)
    objSheet.Cells(17 + k, 2).Value = ArrayRev(i)
  End If
Next i

That was fantastic Peter_SSs thank you

 

[Peter_SSs]

That was fantastic Peter_SSs thank you

You're welcome. Glad it helped.

BTW, about how many rows of data does this write back to objSheet from row 17 down?