0

Goal

I am trying to put data into my spreadsheet using a two dimensional array which can contain one or many products.

Take a look at my two dimensional array: 2d array structure

Now take a look at my wanted result: 2d array result

I can't seem to correctly place the data into the Excel cells. I thought there was a 1 liner of code that could do this, but I tried a different approach and just can't seem to wrap my head around these 2d arrays... Friday can't come soon enough!

Take a look at my code:

If wSheet IsNot Nothing Then
    Dim colRange() As String = {"B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U"}

    For i As Integer = products.GetLowerBound(0) To products.GetUpperBound(0)
        'Start at B2 ... C2 ... D2 ...
        Dim r As Microsoft.Office.Interop.Excel.Range = wSheet.Range(colRange(i) & "2").Resize(products.GetLength(1))
        r.Value2 = products
    Next
End If

This really just doesn'T work, it will just display the (0,0), (1,0) and (2,0) values to the Excel sheet... Any suggestions?

Alex
  • 4,821
  • 16
  • 65
  • 106
  • You will need to Transpose the array, and Resize the range to set Rows and Columns size. Also no need for a `For` loop. Something like `Dim r As Microsoft.Office.Interop.Excel.Range = wSheet.Range("A2").Resize(products.GetLength(1), products.GetLength(0)) r.Value2 = Microsoft.Office.Interop.Excel.Application.transpose(products)` – chris neilsen Nov 17 '16 at 20:55

1 Answers1

0

Bah, I knew it could've been done in a 1 liner! Lol. I swapped the columns / rows in my 2d array and tried to set that as the range.

Then it was just a matter of:

wSheet.Range("B2").Resize(UBound(products, 1), UBound(products, 2)).Value2 = products
Alex
  • 4,821
  • 16
  • 65
  • 106
  • 1
    I've a vague memory that the `.Value2` property is better, but I've forgotten exactly why. http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 – SSS Nov 18 '16 at 04:01