How can I optimize my code

[viettest]

I'm going to try asking my question again as I can't come up with a faster solution for my code. Ill attach a sample of the excel sheet.

[TABLE="width: 608"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Period[/TD]
[TD]Backlog at the start of the month[/TD]
[TD]Volume Receive during the month[/TD]
[TD]Incident Completed during the month[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Apr-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]May-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jun-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jul-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Aug-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sep-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Oct-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Nov-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dec-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jan-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Feb-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mar-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Apr-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]May-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jun-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jul-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Aug-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sep-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Oct-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Nov-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dec-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jan-19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Feb-19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mar-19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Apr-19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

This is the table I am trying to populate. I've already written the macro for it as you can see in Module 1. The problem I'm having is speed. The original data set has over 300,000 rows and this code takes over 30 seconds to run. This is just one section in one table and there are many tables to populate. I'm trying to see how others would approach tackling this type of problem. Please let me know if you require more information.

 

[viettest]

I forgot to add a sample of the data and macro

[TABLE="width: 316"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Date Logged[/TD]
[TD]Date Resolved[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]43123.40227[/TD]
[TD]43123.46993[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]41904.53954[/TD]
[TD]43010.32183[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]41941.54383[/TD]
[TD]42873.2952[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]42055.62597[/TD]
[TD]42922.37431[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]42067.59779[/TD]
[TD]42922.33264[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]42072.46549[/TD]
[TD]43217.52677[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]42072.47997[/TD]
[TD]42993.49816[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]42095.56847[/TD]
[TD]43202.58617[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]42103.46568[/TD]
[TD]43067.52628[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]42124.57968[/TD]
[TD]42865.46007[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]42129.62002[/TD]
[TD]42922.55455[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]42131.41234[/TD]
[TD]43046.37365[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]42139.46682[/TD]
[TD]43201.48561[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]42187.50344[/TD]
[TD]43223.59108[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]42262.42084[/TD]
[TD]43243.4297[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]42276.33565[/TD]
[TD]42936.65436[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]42276.4942[/TD]
[TD]42922.37431[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]42285.55539[/TD]
[TD]43432.64684[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]42296.6516[/TD]
[TD]42922.33264[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]42299.50698[/TD]
[TD]42837.47481[/TD]
[/TR]
</tbody>[/TABLE]


Macro

Sub TableData()


With Application
.Calculation = xlCalculationManual
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
End With


Dim wb As Workbook: Set wb = ActiveWorkbook
Dim wsData As Worksheet: Set wsData = wb.Sheets("Data")
Dim wsTable As Worksheet: Set wsTable = wb.Sheets("Table")


With wsData
DataArray = .Range("A1").CurrentRegion.Value2
DateLoggedCol = .Cells.Find("Date Logged").Address(ReferenceStyle:=xlR1C1): DateLoggedCol = Mid(DateLoggedCol, InStr(DateLoggedCol, "C") + 1, 3)
DateResolvedCol = .Cells.Find("Date Resolved").Address(ReferenceStyle:=xlR1C1): DateResolvedCol = Mid(DateResolvedCol, InStr(DateResolvedCol, "C") + 1, 3)
End With


With wsTable
Table1Array = .Range("A1").CurrentRegion.Value2
End With


For i = 2 To UBound(DataArray)
DateResBlank = IsEmpty(DataArray(i, DateResolvedCol))
For j = 3 To UBound(Table1Array)
Period = Table1Array(j, 1)
NextPeriod = DateAdd("m", 1, Period)
If DataArray(i, DateLoggedCol) < Period And (DataArray(i, DateResolvedCol) >= Period Or DataArray(i, DateResolvedCol) = "") Then
Table1Array(j, 2) = Table1Array(j, 2) + 1
End If
If DataArray(i, DateLoggedCol) > Period And DataArray(i, DateLoggedCol) < NextPeriod Then
Table1Array(j, 3) = Table1Array(j, 3) + 1
End If
If DataArray(i, DateResolvedCol) > Period And DataArray(i, DateResolvedCol) < NextPeriod Then
Table1Array(j, 4) = Table1Array(j, 4) + 1
End If
Next j
Next i


With wsTable
.Range("A1").CurrentRegion.Value2 = Table1Array
End With


With Application
.Calculation = xlCalculationManual
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
End With


End Sub

 

[viettest]

Am I allowed to bump my thread?