How to insert array into range
 

Hi

I have a Visual Basic program (Visual basic.NET 2005) where I automate
Excel (2003).
In the program, I have a two dimensional array of strings, that I need
to paste into a range in Excel.
How do I do it?

Best regads, Lars Olsen
Denmark

How to insert array into range
 

Sub Macro1()
Dim s(10, 10) As String
For i = 1 To 10
For j = 1 To 10
Cells(i, j).Value = s(i, j)
Next
Next
End Sub

Use the above for a range of the same dimensions as the string array.
Or use an Offset if you need it.
--
Gary''s Student


" wrote:

Hi

I have a Visual Basic program (Visual basic.NET 2005) where I automate
Excel (2003).
In the program, I have a two dimensional array of strings, that I need
to paste into a range in Excel.
How do I do it?

Best regads, Lars Olsen
Denmark

How to insert array into range
 

"Gary''s Student" wrote in message
...
Sub Macro1()
Dim s(10, 10) As String
For i = 1 To 10
For j = 1 To 10
Cells(i, j).Value = s(i, j)
Next
Next
End Sub


Hi Gary,

It much easier, and a whole lot faster to create a reference to a range
object with the same dimensions as the array, and then use the Value
property:

Private Sub Test()
Dim str(1 To 7, 1 To 2) As String
Dim i As Integer
For i = 1 To 7
str(i, 1) = Format(i, "dddd")
str(i, 2) = Format(i * 29, "mmmm")
Next
With ThisWorkbook.Worksheets(1)
.Range(.Cells(14, 3), _
.Cells(14 + 6, 3 + 1)).Value = str
End With
End Sub

The OP will have to translate to VB.NET - the range reference and the
dimensions of the array (all arrays are zero based in .NET).

Robert

How to insert array into range
 

Hi Robert

I tried this and it worked nicely. Thanks :o)

/Lars

How to insert array into range
 

Another option to this excellent idea is to use Resize. This lets Excel do
the hard math...

Private Sub Test()
Dim str(1 To 7, 1 To 2) As String
Dim r As Integer
For r = 1 To 7
str(r, 1) = Format(r, "dddd")
str(r, 2) = Format(r * 29, "mmmm")
Next

Range("A14").Resize(UBound(str, 1), UBound(str, 2)) = str
End Sub

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


wrote in message
oups.com...
Hi Robert

I tried this and it worked nicely. Thanks :o)

/Lars

How to insert array into range
 

Hi Dana,


Another option to this excellent idea is to use Resize.

A very good option. Much more intuitive to those not ingrained in doing it
the other way. You just need to be aware that the Resize property doesn't
actually /Resize/ anything, but returns a completely different range object.
Why use a verb for a property name, MS?

This lets Excel do
the hard math...

What values of 'hard' are you using? ;-)

Rob

How to insert array into range
 

Hi. Well, maybe that wasn't a good example. :)
What I was thinking was something along this line...

Dim v(1 To 189, 1 To 127)

1: Range("AF143:FA330") = v
2: Range("AF143").Resize(189, 127) = v

Both options put the array 'v' into the spreadsheet.
For me, I have a "hard" time looking at option 1 as telling if it is
correct.
In fact, it's off by 1 in both dimensions.
Again, it was just another option for the op.
--

Dana DeLouis
Windows XP, Office 2003


"Robert ap Rhys" <killerthewhale [at] yahoo [dot] com wrote in message
...

Hi Dana,


Another option to this excellent idea is to use Resize.

A very good option. Much more intuitive to those not ingrained in doing it
the other way. You just need to be aware that the Resize property doesn't
actually /Resize/ anything, but returns a completely different range
object.
Why use a verb for a property name, MS?

This lets Excel do
the hard math...

What values of 'hard' are you using? ;-)

Rob

How to insert array into range
 

And if you had:

dim v(-12 to 32, 372 to 424)

it would be even more difficult for lots of humans, well, at least me.



Dana DeLouis wrote:

Hi. Well, maybe that wasn't a good example. :)
What I was thinking was something along this line...

Dim v(1 To 189, 1 To 127)

1: Range("AF143:FA330") = v
2: Range("AF143").Resize(189, 127) = v

Both options put the array 'v' into the spreadsheet.
For me, I have a "hard" time looking at option 1 as telling if it is
correct.
In fact, it's off by 1 in both dimensions.
Again, it was just another option for the op.
--

Dana DeLouis
Windows XP, Office 2003

"Robert ap Rhys" <killerthewhale [at] yahoo [dot] com wrote in message
...

Hi Dana,


Another option to this excellent idea is to use Resize.

A very good option. Much more intuitive to those not ingrained in doing it
the other way. You just need to be aware that the Resize property doesn't
actually /Resize/ anything, but returns a completely different range
object.
Why use a verb for a property name, MS?

This lets Excel do
the hard math...

What values of 'hard' are you using? ;-)

Rob

--

Dave Peterson