Display Array from VBA

B

BillS

Hi:

At the end of a VBA macro I have a set of results saved in an array e
Results (1 to 100).

How do I display the results in a specified range in a worksheet?

I can do it by looping through and doing each one at a time but wha
line of code would do it in one go (if there is one)?

Thanks

Bil
 
B

BillS

Thanks Norman, but I still seem to be getting only the first element o
the array in all the cells.

Full code is below

Sub Run()
Application.ScreenUpdating = False
NumSims = Range("NumSims")
ReDim Results(1 To NumSims) As Single

Result = 0

For i = 1 To NumSims
Calculate
Result = Result + Range("WinLoss")
Results(i) = Result
Next i

Range("Result").Value = Result

If Range("Chart?").Value = "True" Then
Sheets("Chart").Activate
Range("A:A").ClearContents
Range("A1").Resize(100, 1) = Results
End If

End Sub


Bil
 
N

Norman Jones

Hi Bill,

In tour code change the line:

Range("A1").Resize(100, 1) = Results

to:

Range("A1").Resize(100, 1) =Application.Transpose(Results)
 
A

Alan Beban

Norman said:
Hi Bill,

In tour code change the line:

Range("A1").Resize(100, 1) = Results

to:

Range("A1").Resize(100, 1) =Application.Transpose(Results)
Click to expand...

Or Range("A1").Resize(1, 100).Value = Results

Alan Beban
 
B

BillS

Now it works. Many thanks both for your time.

I don't quite understand how the resize function works here though.

Bil
 
A

Alan Beban

From the online help:

Resize Property


Resizes the specified range. Returns a Range object that represents the
resized range.

Syntax

expression.Resize(RowSize, ColumnSize)

expression Required. An expression that returns a Range object to be
resized.

RowSize Optional Variant. The number of rows in the new range. If this
argument is omitted, the number of rows in the range remains the same.

ColumnSize Optional Variant. The number of columns in the new range.
If this argument is omitted, the number of columns in the range remains
the same.

Alan Beban
 
Top