Databases
StackOverflow2013
Postsdbo
POReduce for loop execution time that returns a count
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReduce for loop execution time that returns a count
    primarykey
    data
    text
    <p><strong>What I am trying to achieve</strong></p> <p>I have two sheets: 'dashboard' and 'temp calc'.<br> Dashboard has all employee details and range "N1" "N2" contain dates.<br> Now a macro populates employee data and generates a daywise calendar as shown in the following image <img src="https://i.stack.imgur.com/1fdKT.png" alt="sample image"> 'temp calc' has their project details with start date end date.(the date that do not fall between n1 and n2 dates from dashboard sheet are deleted here).</p> <p>So now referencing their empid from dashboard sheet, and using the first day populated in dashboard sheet i loop through the emp id in temp calc sheet and return a count for the number of projects a employee is currently working on for the particular day. as shown in the following image.</p> <p><img src="https://i.stack.imgur.com/11BWR.png" alt="sample image"></p> <p><strong>how I achieve this:</strong></p> <p>the code.....</p> <pre><code>Option Explicit Sub Count() ' x= no of columns(dashboard calender) ' y= no of rows(dashboard emp id) ' z= no of rows(temp calc sheet emp id) Application.ScreenUpdating = False 'Clear calender data Range("Q4").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.ClearContents Dim i, j, k, l, d, x, y, z, Empid As Long Dim currentdate, startdate, enddate As Date x = (Range("n2") - Range("n1")) + 1 y = Application.WorksheetFunction.counta(Range("A:A")) - 1 z = Application.WorksheetFunction.counta(Worksheets("Temp Calc").Range("A:A")) - 1 For i = 1 To y Step 1 'To loop through the emp_id in dashboard. For j = 1 To x Step 1 'To loop through the calender in dashboard daywise. d = 0 For k = 1 To z Step 1 'To loop through the emp_id i temp calc sheet. Empid = ActiveSheet.Cells(i + 3, 1).Value currentdate = Cells(3, 16 + j).Value startdate = Worksheets("Temp calc").Cells(k + 1, 3).Value enddate = Worksheets("Temp calc").Cells(k + 1, 4).Value If (Worksheets("Temp calc").Cells(k + 1, 1).Value) = Empid Then If (currentdate &gt;= startdate) And (currentdate &lt;= enddate) Then 'To check whether the first column date falls within the project start and end date d = d + 1 End If End If Next Worksheets("Dashboard").Cells(i + 3, j + 16) = d Next Next Range("q4").Select Application.ScreenUpdating = True End Sub </code></pre> <p><strong>My problem:</strong> The code does the job,but I have two problems.</p> <ol> <li><p>It is too slow</p></li> <li><p>Sometimes the workbook will say not responding and won't do the work.I've checked it does not work in the background. I left the program running overnight and it went into not responding.</p></li> </ol> <p><strong>Possible solutions</strong>:</p> <ol> <li><p>using two arrays: one array to store empid in dashboard,second array to store calendar generated in dashboard. and then compare it with data from temp calc sheet and return a count into array number 2 and write it back the problem is I've just started reading about arrays and I am still learning</p></li> <li><p>I am open to possible alternatives:</p></li> </ol> <p>cheers,<br> mathew</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USmathew
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POReduce for loop execution time that returns a count
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload