Insert into a range from a two dimensional array

  • Thread starter Thread starter gimme_this_gimme_that
  • Start date Start date
G

gimme_this_gimme_that

Suppose I want to have cells A1 through B3 assigned the following
values:

2,4,8
2, 8,11

How do I create the right hand side of this statement?

Sub test()
Dim b As Workbook
Dim s As Worksheet
Set b = ThisWorkbook
Set s = b.Sheets("Sheet1")
s.Activate
Dim v
v = Array(2, 4, 8)
Dim s
s = Array(2,8,11)

'How to make v and s a two dimensional array?

Dim r As Range
Set r = s.Range(s.Cells(1, 1), s.Cells(2, 3))
r.Value = ??????? 'Assignment here wanted either in terms of v and
s or their 2 dimensional counterpart

End Sub

Don't bother posting a looping solution.

Thanks.
 
Modify to suit. Sounds like homework?
Don't bother posting a looping solution.
Click to expand...

Sub fillup()
Range("a2:b3").Value = "2,4,8" & vbCrLf & "3,4,5"
End Sub

-
Don Guillett
SalesAid Software
(e-mail address removed)
 
not sure, but this one fills the range from a 2D-array:

Public Sub Test()

Dim wb As Workbook
Dim sh As Worksheet

Dim v(0 To 3, 0 To 3) As Single
Dim r As Range
Dim e As Variant 'element in range
Dim c As Integer 'counter

Set wb = ThisWorkbook
Set sh = wb.Sheets("Sheet1")
Set r = sh.Range("A1:C2")
c = 1

v(1, 0) = 2
v(2, 0) = 4
v(3, 0) = 8

v(0, 1) = 2
v(0, 2) = 8
v(0, 3) = 11

'Looping solution...
For Each e In r

If c <= 3 Then
e.Value = v(c, 0)
ElseIf c > 3 Then
e.Value = v(0, (c - 3))
End If
c = c + 1

Next

End Sub
 
Not sure what exactly you are trying to do, but I don't think it is possible
to
populate a 2-D array from 1-D arrays without loops.
Possibly you could do it with the CopyMemory API, but not sure that is
worth the extra trouble.
What is wrong with using loops?

RBS
 
Thanks for spending the time getting that. But I need a solution that
DOES NOT USE LOOPING.
 
On my machine this results in the string "2,4,8\n3,4,5" being inserted
into 4 cells.
 
If that is the problem then maybe post in the vb.winapi
group and I think it can be done with copymemory.

RBS
 
What do you think of this?


Sub fillup()
Dim b As Workbook
Dim s As Worksheet
Set b = ThisWorkbook
Set s = b.Sheets("Sheet1")
s.Range(s.Cells(1, 1), s.Cells(2, 3)).Value =
Evaluate("{2,4,8;3,4,5}")
End Sub
 
Unless it is a homework task then what is the point of the whole exercise?
If you are hard-coding your data then I can't see much use for it.

RBS
 
I don't know of a single college course where you can write VBA and
get credit for it.

I'm guessing you've never even been to college and here you are
trolling a Microsoft newsgroup..

Anyway read my google profile. I'm working on Perl/Applescript/Excel
stuff. Nothing you'd to in college.
 
Range("a1:a3") = Application.Transpose(v)
Range("b1:b3") = Application.Transpose(s)

Alan Beban
 
I somehow thought you wanted to put 1-D arrays in a 2-D array.
Maybe you should learn to explain your questions a bit clearer.

RBS
 
Back
Top