Populating 2D Array with Loop(s)

Caleeco

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
980
Office Version
  1. 2010
Platform
  1. Windows
Hello,

I'm struggling with setting up a 2D array, and populating it with an loop

My sheet is set out as follows:
Excel 2012
KLMNOPQRSTUVWXYZAAABACADAEAFAG
000000
3030303030

<tbody>
[TD="align: center"]7[/TD]
[TD="bgcolor: #EAF3FA, align: right"]08[/TD]
[TD="bgcolor: #EAF3FA, align: center"]:[/TD]
[TD="bgcolor: #EAF3FA, align: right"]30[/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]08[/TD]
[TD="bgcolor: #EAF3FA, align: center"]:[/TD]
[TD="bgcolor: #EAF3FA, align: right"]30[/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]08[/TD]
[TD="bgcolor: #EAF3FA, align: center"]:[/TD]
[TD="bgcolor: #EAF3FA"]30[/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]08[/TD]
[TD="bgcolor: #EAF3FA, align: center"]:[/TD]
[TD="bgcolor: #EAF3FA"]30[/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]08[/TD]
[TD="bgcolor: #EAF3FA, align: center"]:[/TD]
[TD="bgcolor: #EAF3FA"]30[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: center"]:[/TD]
[TD="align: right"]00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: center"]:[/TD]
[TD="align: right"]00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: center"]:[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: center"]:[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: center"]:[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #EAF3FA, align: right"]30[/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA"]Min[/TD]
[TD="bgcolor: #EAF3FA, align: center"][/TD]
[TD="bgcolor: #EAF3FA, align: center"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]30[/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA"]Min[/TD]
[TD="bgcolor: #EAF3FA, align: center"][/TD]
[TD="bgcolor: #EAF3FA, align: center"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]30[/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA"]Min[/TD]
[TD="bgcolor: #EAF3FA, align: center"][/TD]
[TD="bgcolor: #EAF3FA, align: center"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]30[/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA"]Min[/TD]
[TD="bgcolor: #EAF3FA, align: center"][/TD]
[TD="bgcolor: #EAF3FA, align: center"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]30[/TD]
[TD="bgcolor: #EAF3FA, align: right"][/TD]
[TD="bgcolor: #EAF3FA"]Min[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: center"]:[/TD]

[TD="align: right"]12[/TD]
[TD="align: center"]:[/TD]

[TD="align: right"]12[/TD]
[TD="align: center"]:[/TD]

[TD="align: right"]12[/TD]
[TD="align: center"]:[/TD]

[TD="align: right"]12[/TD]
[TD="align: center"]:[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #EAF3FA, align: right"]17[/TD]
[TD="bgcolor: #EAF3FA, align: center"]:[/TD]
[TD="bgcolor: #EAF3FA"]00[/TD]
[TD="bgcolor: #EAF3FA"][/TD]
[TD="bgcolor: #EAF3FA"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]17[/TD]
[TD="bgcolor: #EAF3FA, align: center"]:[/TD]
[TD="bgcolor: #EAF3FA"]00[/TD]
[TD="bgcolor: #EAF3FA"][/TD]
[TD="bgcolor: #EAF3FA"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]17[/TD]
[TD="bgcolor: #EAF3FA, align: center"]:[/TD]
[TD="bgcolor: #EAF3FA"]00[/TD]
[TD="bgcolor: #EAF3FA"][/TD]
[TD="bgcolor: #EAF3FA"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]17[/TD]
[TD="bgcolor: #EAF3FA, align: center"]:[/TD]
[TD="bgcolor: #EAF3FA"]00[/TD]
[TD="bgcolor: #EAF3FA"][/TD]
[TD="bgcolor: #EAF3FA"][/TD]
[TD="bgcolor: #EAF3FA, align: right"]17[/TD]
[TD="bgcolor: #EAF3FA, align: center"]:[/TD]
[TD="bgcolor: #EAF3FA"]30[/TD]

</tbody>
Timesheet​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K9[/TH]
[TD="align: left"]=(TIMEVALUE(K10&":"&M10)-TIMEVALUE(K8&":"&M8))*24*60[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]P9[/TH]
[TD="align: left"]=(TIMEVALUE(P10&":"&R10)-TIMEVALUE(P8&":"&R8))*24*60[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]U9[/TH]
[TD="align: left"]=(TIMEVALUE(U10&":"&W10)-TIMEVALUE(U8&":"&W8))*24*60[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Z9[/TH]
[TD="align: left"]=(TIMEVALUE(Z10&":"&AB10)-TIMEVALUE(Z8&":"&AB8))*24*60[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AE9[/TH]
[TD="align: left"]=(TIMEVALUE(AE10&":"&AG10)-TIMEVALUE(AE8&":"&AG8))*24*60[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

What I wish to do, is capture all the entered Hours and minutes in a 2D array. Row 9 needs to be ignored. Eg. the first columns of hours and minutes can be captured as
Code: 
myarr(1, 1) = Cells(7, 11)
myarr(2, 1) = Cells(7, 13)
myarr(3, 1) = Cells(8, 11)
myarr(4, 1) = Cells(8, 13)
'
myarr(5, 1) = Cells(10, 11)
myarr(6, 1) = Cells(10, 13)
myarr(7, 1) = Cells(11, 11)
myarr(8, 1) = Cells(11, 13)

and then the second column of hours and minutes would be

Code: 
myarr(1, 1) = Cells(7, 16)
myarr(2, 1) = Cells(7, 18)
myarr(3, 1) = Cells(8, 16)
myarr(4, 1) = Cells(8, 18)
'
myarr(5, 1) = Cells(10, 16)
myarr(6, 1) = Cells(10, 18)
myarr(7, 1) = Cells(11, 16)
myarr(8, 1) = Cells(11, 18)

an so fourth until the 5th column. Is there a way to setup an efficient loop? Open to changing array structure, eg:
Code: 
myarr(1 to 40) as string

The intent is to capture the data, paste it to another sheet, and then be able to recall the times and paste in their respective locations.

Any help would be appreciated
Thanks

Caleeco
 
Last edited:
Sort by date Sort by votes
Caleeco said:
The intent is to capture the data, paste it to another sheet, and then be able to recall the times and paste in their respective locations.
Click to expand...
You may be able to simplify your approach depending on exactly what you meant by the above. Can you explain the "paste in their respective locations" part in more detail please?
 
Upvote 0
Rick Rothstein said:
You may be able to simplify your approach depending on exactly what you meant by the above. Can you explain the "paste in their respective locations" part in more detail please?
Click to expand...

Hi Rick,

Thanks for stopping by :) I'll be happy to clarify. So if the sheet shown above is on Sheets("Timesheet")

The process would be as follows
1. User enters minutes and hours for the week on the sheet 'Timesheet'
2. At the end of the week, these numbers need to be captured/logged on another sheet called 'Record', shown below with Monday Week 1 completed.
264rmgz.png


3. The following monday all entries are cleared from the sheet (as a new week has begun).
4. I will then allow the user to recall data that has been logged for any week at a later date by populating the data stored in the "Record Sheet" in another array.
5. And then pasting that data back into the "Timesheet" sheet in their respective locations (Eg arranged as shown in the original post)

Hopefully that made sense, if not I can provide a more detailed explanation.

I am also open to any suggestions you may have to make this simpler!

Many Thanks
Caleeco
 
Last edited:
Upvote 0
Does this macro do what you want...
Code: 
Sub MoveTimesheetInfoToRecordSheet()
  Dim X As Long, R As Long, C As Long, Col As Long, NextRow As Long
  Dim TS As Worksheet, RS As Worksheet
  Set TS = Sheets("Timesheet")
  Set RS = Sheets("Record")
  NextRow = RS.Cells(Rows.Count, "B").End(xlUp).Row + 1
  For Col = 11 To 41 Step 5
    For R = 7 To 11
      If R <> 9 Then
        C = C + 2
        RS.Cells(NextRow, C) = TS.Cells(R, Col)
        RS.Cells(NextRow, C + 1) = TS.Cells(R, Col + 2)
      End If
    Next
  Next
  RS.Cells(NextRow, "B").Resize(, 40).NumberFormat = "00"
End Sub
 
Upvote 0
^^ Amazing. Thank you for that Rick, it works wonders. Highly efficient bit of code :)

Would you be able to code something to do the reverse? i.e Locate the data for a particular week on the RECORD sheet and paste it back onto the TIMESHEET sheet?
Code below would do the basic find I believe:

Code: 
Sub MoveRecordInfoToTimesheetSheet()
  Dim TS As Worksheet, RS As Worksheet
  Dim Week As Long
  Dim RNG As Range, c As Range
  Set TS = Sheets("Timesheet")
  Set RS = Sheets("Record")
  Set RNG = RS.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
  
  'The week number to find is contained in the cell below
  Week = TS.Range("G5").Value
  
  Set c = RNG.Find(Week)
  If Not c Is Nothing Then
    MsgBox c.Row
    ' Paste the data found from this row on RECORD sheet into the TIMESHEET Sheet


  End If


End Sub

You help with this is much appreciated!
Many Thanks

Caleeco
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help
Replies
1
Views
125
6StringJazzer
6StringJazzer
C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
272
CaptainGravyBum
C
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
500
myall_blues
myall_blues
C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
225
cooleomter
C
M
  • Question Question
Random SUM calculation issue in Pivot Table
Replies
1
Views
112
arthurbr
arthurbr

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top