CODEWITHSUNDEEP
arraysexcelvba
Pregunto
Pregunto

Reputation: 111

printing an array onto a worksheet

I am totally new to VBA and have the following question:

I need to create a macro that prints an array of the first 10 even numbers onto a worksheet.

I came up with the following, but I do not know how to actually get it printed onto the worksheet (i.e. the last step). Can anyone help out?

Sub FirstTenEvenNumbers()
Dim a(1 To 10) As Integer

a(1) = 2
a(2) = 4
a(3) = 6
a(4) = 8
a(5) = 10
a(6) = 12
a(7) = 14
a(8) = 16
a(9) = 18
a(10) = 20

End Sub

Thank you!

Upvotes: 0

Views: 94

Answers (4)

DisplayName
DisplayName

Reputation: 13386

You could use Excel ROW() function:

Sub FirstTenEvenNumbers()
    With Range("A1:A10")
        .Formula = "= 2 * ROW()"
        .Value = .Value
    End With
End Sub

With an easy twick should the pasting Range begin from a even row

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54807

Pasting an Array Into a Range

Sub FirstTenEvenNumbers()

  Const cVntName As Variant = "Sheet1"    ' Worksheet Name/Index
  Const cStrRange As String = "A1"        ' Paste Range
  Const cIntNum As Integer = 10           ' Number of Values

  Dim vntNum As Variant                   ' Array of Values
  Dim intCount As Integer                 ' Values Counter, Array Rows Counter

  ' Resize the array to a one-column one-based two-dimensional array.
  ' The first 1 means one-base and the cIntNum is the size of the first
  ' dimension, the upper bound (UBound).
  ' The second 1 means the second dimension is one-based and the last 1 one
  ' means one column.
  ' This array is purposly constructed in this way to be able to paste it
  ' into a range.
  ReDim vntNum(1 To cIntNum, 1 To 1)

  ' Loop through the values.
  For intCount = 1 To cIntNum
  ' Write calculated results (2 * intCount) to array (vntNum).
    vntNum(intCount, 1) = 2 * intCount
  Next


  With ThisWorkbook.Worksheets(cVntName)
  ' Resize the cell range to the size of the array and paste they array into it.
    .Range(cStrRange).Resize(UBound(vntNum), 1) = vntNum
  End With

End Sub

Upvotes: 2

QHarr
QHarr

Reputation: 84465

You can write direct to sheet using the ubound of the array dimensions (use Transpose if want as rows)

Public Sub FirstTenEvenNumbers()
    Dim a(1 To 10) As Integer

    a(1) = 2
    a(2) = 4
    a(3) = 6
    a(4) = 8
    a(5) = 10
    a(6) = 12
    a(7) = 14
    a(8) = 16
    a(9) = 18
    a(10) = 20

    ActiveSheet.Range("A1").Resize(UBound(a), 1) = Application.Transpose(a) 'rows
    ActiveSheet.Range("C1").Resize(1, UBound(a)) = a 'columns
End Sub

Upvotes: 4

Paolo
Paolo

Reputation: 15827

Simply

ActiveSheet.Cells( y, x ).Value = n

where y is the row index, x the column index, n the value the cell has to be set to.

To achieve what you need why not use a for loop ?

Sub FirstTenEvenNumbers()
    for i = 1 to 10
        ActiveSheet.Cells( i, 1 ).Value = i * 2
    next i
End Sub

Upvotes: 0

Related Questions