Load Array with Values on Excel worksheet

Looking to load array in Excel.  Will be assigning this code to a command button.

When selecting the command button all the values will be loaded into an array.

Sheet1
values D5 to ND5

I am just getting back into programming and need an model to get me going.  Thanks to anyone that can assist.
Bob StammOperations ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Quite straightforward really.
Dim arrData As Variant

    arrData = Sheets("Sheet1").Range("D5:ND5").Value ' fill array with data from N5:D5

Open in new window


Note the above code will produce a 2-dimensional array with the dimensions 1 to 1, 1 to 365, i.e. 1 row and 365 columns, if you like.
0
aikimarkCommented:
0
Bob StammOperations ManagerAuthor Commented:
Thank you...that has me going.  I tosses in a msgbox to make sure I was getting the data loaded.  

thanks again and for the article too.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bob StammOperations ManagerAuthor Commented:
I now want to place my data on the "Print Calendar" worksheet.  The fill data array captured all the data for 365 days (works great).  I just want to place the first 31 days of data on the "Print Calendar" worksheet.  I assuming I need some type of loop or is there a more direct statement?  

Dim arrData As Variant

    arrData = Sheets("Calendar").Range("D5:ND5").Value ' fill array with data from D5:ND5

'This statement I wrote places the same value in each cell (I need help here).  
    Sheets("Print Calendar").Range("B55:AF55").Value = arrData(1, 2) ' Place calendar data for January
   
End Sub
0
NorieVBA ExpertCommented:
Try this.
    Sheets("Print Calendar").Range("B55").Resize(,31).Value = arrYear

Open in new window

Note, I've used Resize(,31) to expand the destination range for the data by 31 columns.
0
Bob StammOperations ManagerAuthor Commented:
Starting to get there.  If I need to grab from 32 to 60 out of arrData how is that done?  This would be for my month of February.

Dim arrData As Variant
    arrData = Sheets("Calendar").Range("D5:ND5").Value ' fill array with data from D5:ND5

    Sheets("Print Calendar").Range("B55").Resize(,31).Value = arrData ' January Data
    Sheets("Print Calendar").Range("B56").....???                                       ' February Data
End Sub
0
NorieVBA ExpertCommented:
Bob

Unfortunately that isn't so straightforward and my previous suggestion probably wasn't the best example.

You could try this function.
Function SliceArray(arr As Variant, StartIndex As Long, EndIndex As Long) As Variant
' slices single column/row 1-indexed array

    If UBound(arr, 1) >= 1 Then
        SliceArray = Application.Index(arr, 1, Evaluate("Transpose(Row(" & StartIndex & ":" & EndIndex & "))"))
    ElseIf UBound(arr, 2) = 1 Then
        SliceArray = Application.Index(arr, Evaluate("Transpose(Row(" & StartIndex & ":" & EndIndex & "))"), 1)
    Else
        SliceArray = "Error!"
    End If
    
End Function

Open in new window


Which you could use like this.
Sub TestSlice()
Dim arrYearData As Variant
Dim arrJanData As Variant
Dim arrFebData As Variant

    arrYearData = Sheets("Calendar").Range("D5:ND5").Value

    arrJanData = SliceArray(arrYearData, 1, 31)
    arrFebData = SliceArray(arrYearData, 32, 59)

    Sheets("Print Calendar").Range("B55").Resize(, 31).Value = arrJanData
    Sheets("Print Calendar").Range("B56").Resize(, 28).Value = arrFebData
    
End Sub

Open in new window


Note, I've adjusted the end index for February because I used data from this year.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob StammOperations ManagerAuthor Commented:
This worked great.  Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.