add row from 2-densional array to excel range

[neemi]

I have populated a 2 dimensional array in Excel vba and need to add the values to a range in excel and need help in doing this:

What I have so far is:

.Range("C1:I7").Value = vArrayTotals

where vArrayTotals is the array vArrayTotals(1 to 2, 1 to 7)

I want to add the second row from the array to the range, Range("C1:I7")?

How do I do this?

Help appreciated?
regards,
Neemi

 

[nickdel]

Neemi, apologies if I've picked you up wrong. What you want to do is build a string from your array and then assign it to the value of the cell.

For a = 1 To UBound(vArrayTotals)
    
    MyString = MyString & vArrayTotals(1, a) & " " & vArrayTotals(2, a)
    
Next

Range("C1:I7").Value = MyString

Cheers

Nick

 

[neemi]

Thanks for the response nickdel. I ended up just created 2 seperate 1d arrays as I just needed to pass through to rows, and then using something like

.Range("C1:I7").Value = vArrayTotals

I think for the purpose of what I need to do, as it is only 2 rows, this makes life simpler and easier.

Thanks again for the quick response anyway.
neemi

 

[N1GHTEYES]

I'm not sure exactly what you are trying to do. You say you want to add the data from an array in code to a range on a spreadsheet, but you want to do it one row at a time.

However, the range to which you are assigning the data has 7 rows and 7 columns, whereas the array has 2 rows and 7 columns.

If you simply want to assign the data from the array into a range, you can do so in one line. You should however ensure that the rows and columns in the range and the array are the same.

Here is a simple example which populates an array then assigns it to a range.

Dim vArrayTotals(1 To 2, 1 To 7)
Dim i As Long, j As Long, counter As Long

'fill the array with dummy data
For i = 1 To 2
  For j = 1 To 7
    counter = counter + 1
    vArrayTotals(i, j) = counter
  Next j
Next i


'assign the data to a range
Range(Cells(1, 1), Cells(2, 7)) = vArrayTotals

I hope that helps.

Tony

 

[firefytr]

Your dimensions do not match. Your string range is 7 rows by 7 columns, but your array is only 2 rows by 7 columns.

If your array or range will be variable in length, there are ways to get the dimensions of an array via functions, but it is somewhat convuluted (IMO), but it can be done.

Get your ranges straightened out and you should be fine.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[neemi]

Sorry my mistake....
the range should read Range("C1:I1")!!!
and I want to populate this with 1 row from the array.

 

[Bong]

I don't understand the magic here. Consider this:

Sub a2r()
    Dim arA(8, 5) As Integer
    For i = 0 To 8
        For j = 0 To 5
            arA(i, j) = i + j
        Next
    Next
    [red][a1:f9] = arA[/red]
End Sub

this produces (as expected):
0 1 2 3 4 5
1 2 3 4 5 6
2 3 4 5 6 7
3 4 5 6 7 8
4 5 6 7 8 9
5 6 7 8 9 10
6 7 8 9 10 11
7 8 9 10 11 12
8 9 10 11 12 13


_________________
Bob Rashkin

 

[firefytr]

Then you need to ReDim your array to match the range dimensions. Also take a look at the Preserve keyword. Looping through one array and setting the values of another (one to be used to set a range of values) might be another way to do it, but if you can avoid looping you're generally better off.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci