CODEWITHSUNDEEP
arraysvbaexcel
user1681664
user1681664

Reputation: 1811

simple worksheet loop and put cell value into vba array

I want to do a very simple program, but for some reason it is not working. I want to loop through every worksheet in my workbook and get the cell value of O13 and put it in an array. then print the entire array in 1 worksheet. For some reason, it doesn't read in the number. I tried setting the array to variant, integer, and double, nothing works.

     Dim WS_Count As Integer

     Dim I As Integer

     WS_Count = ActiveWorkbook.Worksheets.Count

     Dim array1(43) As Double

     For I = 1 To WS_Count

        array1(I) = ActiveWorkbook.Worksheets(I).Cells(13, 15).Value

     Next I

     Range("A40") = array1

Upvotes: 1

Views: 317

Answers (3)

Juanpablomo
Juanpablomo

Reputation: 71

You need start the array in 1 Dim array1(1 to 43) As Double and after set the range to put the array and use Transpose Range("A40:A83") = Application.Transpose(array1)

Upvotes: 2

Vityata
Vityata

Reputation: 43595

This works by me:

Public Sub SWL()

    Dim WS_Count    As Long
    Dim I           As Long
    Dim array1(43)  As String
    Dim str         As String

    WS_Count = ActiveWorkbook.Worksheets.Count

    For I = 1 To WS_Count

       array1(I) = ActiveWorkbook.Worksheets(I).Cells(1, 1).Value

    Next I

    str = CStr(Join(array1, " "))
    ActiveWorkbook.Worksheets(1).Cells(1, 2) = str

End Sub

Upvotes: 1

luke_t
luke_t

Reputation: 2985

Use the WorksheetFunction.Transpose method to transpose the array into the range you wish to output into.

Range("A40:A83") = WorksheetFunction.Transpose(array1)

Upvotes: 0

Related Questions