Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert array into range
Hi Robert
I tried this and it worked nicely. Thanks :o) /Lars |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Let user insert value that highlights closest value in an array. | Excel Discussion (Misc queries) | |||
Syntax to insert an array formula in a cell | Excel Programming | |||
How do I always insert last cell from a increasing array ? | Excel Worksheet Functions | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming | |||
Insert Rows into an existing array | New Users to Excel |