Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Copying array to cells - simple question

Member Posts: 9
I can read cell values into variant arrays without a problem but have not succeeded in the opposite direction, that is, copying array values (integers or whatever) to worksheet cells.

My rudimentary question is why the below routine returns zeros instead of the numbers 2, 4, 6. I have tried declaring the array as Variant without improvement. Quick help appreciated.
[code]
Dim i As Integer
Dim MyArr(3) As Integer

For i = 1 To 3
MyArr(i) = i * 2
Next i
Range("B1:B3").Value = MyArr ' returns zeros
[/code]

• Member Posts: 19
[b][red]This message was edited by JavaJen at 2002-11-4 21:43:25[/red][/b][hr]
Try this:

Public Sub MyMacro()
Dim sht As Worksheet
Set sht = Worksheets(1)
Dim i As Integer
Dim MyArr(3) As Integer

For i = 1 To 3
MyArr(i) = i * 2
sht.Range("B" & i).Value = MyArr(i)
Next i
End Sub

• Member Posts: 2,900
: I can read cell values into variant arrays without a problem but have not succeeded in the opposite direction, that is, copying array values (integers or whatever) to worksheet cells.
:
: My rudimentary question is why the below routine returns zeros instead of the numbers 2, 4, 6. I have tried declaring the array as Variant without improvement. Quick help appreciated.
: [code]
: Dim i As Integer
: Dim MyArr(3) As Integer
:
: For i = 1 To 3
: MyArr(i) = i * 2
: Next i
: Range("B1:B3").Value = MyArr ' returns zeros
: [/code]

My guess is that MyArr without an index refers to the first element, which is element 0, which you never set to a value. I don't believe that there is any implicit "range" funcionality in VB for dealing with arrays.
• Member Posts: 19
: I don't believe that there is any implicit "range" funcionality in VB for dealing with arrays.
:

Range is not a function in VB, it is a member in the Excel object model used in VBA to reference cells in a worksheet. If you go into Excel, then VBA, and into the Object Browser(F2) you will find Range in the library.

• Member Posts: 139
Try this:
[code]
[blue]Dim[/blue] i [blue]As Integer[/blue]
[blue]Dim[/blue] MyArr(1 [blue]To[/blue] 3) [blue]As Integer[/blue]
[blue]For[/blue] i = 1 [blue]To[/blue] 3
MyArr(i) = i * 2
[blue]Next[/blue] i
Range("B1:B3").Value = MyArr(1) & ", " & MyArr(2) & ", " & MyArr(3)
[/code]
This should put "2, 4, 6" in the cell.
..:: Zantos ::..
• USAMember Posts: 0

_____ || http://forcoder.org || free ebooks and video tutorials about / MATLAB C++ Perl JavaScript PL/SQL Assembly Objective-C Scratch Swift Java Delphi Ruby R Python C# Visual Basic .NET Go PHP Visual Basic C Bash Transact-SQL Lua Apex F# LabVIEW ML Alice Clojure COBOL Fortran FoxPro Crystal Ada D Scheme SAS Hack Julia VBScript Logo Dart Lisp Erlang Scala ABAP Kotlin Prolog Rust Awk /