Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts

    How to fill an array with VBA

    Hi folks,

    This is my second post so please go easy on me. I have an spreadsheet that increases and decreases with data. Every other row in column A has a date that I am trying to save in an array variable but I can not seem to figure out the correct macro code to do it. If possible, I would like the code to limit the variable size to the amount of data. Please indicate if I have not explained myself clearly. I would appreciate any help

    Alouso

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Alouso,

    Here is code that will set the value of everyother cell in Column A to an array variable Dateval. It will automatically adjust to the number of rows by indexing the cells in column A using the Redim statement. The debug.print loop shows the values of the array variable and can be deleted in your code.

    HTH,
    Maud

    Code:
    Public Sub test()
    
    Dim Dateval() As Variant
    
    LastRow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row  'FIND THE LAST ROW
    ReDim Dateval(1 To LastRow / 2)  'RESIZE THE ARRAY VARIABLE TO HALF THE NUMBR OF LINES
    For I = 2 To LastRow Step 2  'CYCLE THROUGH EVERYOTHER CELL IN COLUMN A
        Dateval(I / 2) = Cells(I, 1).Value  'ASSIGN DATE VALUE TO ARRAY VARIABLE INDEXED BY I/2
    Next I
    
    'PRINTOUT THE ARRAY VARIABLES IN THE IMMEDIATE WINDOW
    For I = 1 To LastRow / 2  
        Debug.Print Dateval(I)
    Next I
    
    End Sub

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2013-05-23)

  4. #3
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Thank you very much Muadibe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •