I have a report that I need to update from the data in another report. Both reports are large, over 50,000 rows, so I read them into arrays so the process would run faster.
I need to split the Source array into separate arrays based on certain conditions in the HR array. I'm getting an object required error when I try to assign a value to the ID variable.
Could anyone help me figure out how to fix this error? Also, am I going about this the right way? Thanks.
Here's my code:
Sample Data:
I need to split the Source array into separate arrays based on certain conditions in the HR array. I'm getting an object required error when I try to assign a value to the ID variable.
Could anyone help me figure out how to fix this error? Also, am I going about this the right way? Thanks.
Here's my code:
VBA Code:
Option Explicit
Sub SearchArrays()
Dim wb As Workbook, wsSource As Worksheet, wsHR As Worksheet
Dim arrSource() As Variant, arrHR() As Variant, arrNotFound() As Variant, arrRemoved() As Variant, arrUpdated() As Variant
'Dim ID As String
Dim ID As Variant
Dim x As Long, y As Long, nCounter As Long, CounterN As Long, rCounter As Long, CounterR As Long, uCounter As Long, CounterU As Long
Set wb = ThisWorkbook
Set wsSource = wb.Worksheets("Source")
Set wsHR = wb.Worksheets("HR")
wsSource.Activate
arrSource = Range("A2", Range("A2").End(xlDown).End(xlToRight)) 'Read Source data into array
wsHR.Activate
arrHR = Range("A2", Range("A2").End(xlDown).End(xlToRight)) 'Read HR data into array
'Use Find to find the values in source array in the hr array
For x = LBound(arrSource, 1) To UBound(arrSource, 1)
For y = LBound(arrHR, 1) To UBound(arrHR, 1)
'ID is in column 2 of Source data and column 3 of HR data
Set ID = arrSource(x, 2).Find(what:=arrHR(y, 3).Value, LookIn:=xlValues, lookat:=xlWhole)
If ID Is Nothing Then
'Copy data to Not Found array
nCounter = nCounter + 1
ReDim Preserve arrNotFound(1 To 5, 1 To nCounter) 'Redimension the Not Found array with each instance
For CounterN = 1 To 5 'The arrNotFound equals the current row
arrNotFound(CounterN, nCounter) = arrSource(x, CounterN)
Next CounterN
ElseIf Not ID Is Nothing And ID.Offset(, 3).Value <> arrHR(y, 3).Offset(, 2) Then
'Copy to removed array
rCounter = rCounter + 1
ReDim Preserve arrRemoved(1 To 5, 1 To rCounter) 'Redimension the Removed array with each instance
For CounterR = 1 To 5 'The arrRemoved equals the current row
arrRemoved(CounterR, rCounter) = arrSource(x, CounterR)
Next CounterR
ElseIf Not ID Is Nothing And ID.Offset(, 3).Value = arrHR(y, 3).Offset(, 2) Then
'Copy to Updated array
uCounter = uCounter + 1
ReDim Preserve arrUpdated(1 To 5, 1 To uCounter) 'Redimension the Updated array with each instance
For CounterU = 1 To 5 'The arrUpdated equals the current row
arrUpdated(CounterU, uCounter) = arrSource(x, CounterU)
Next CounterU
End If
Next y
Next x
'Write arrNotFound to a new worksheet
'Write arrRemoved to a new worksheet
'Write arrUpdated to a new worksheet
End SubSample Data:
| Name | ID | Job Title | Salary | Department |
| Nancy Drew | A0001 | Manager | $ 50,000.00 | Sales |
| Tom Hardy | A0002 | Assistant Manager | $ 35,000.00 | Accounting |
| Bugs Bunny | A0003 | Director | $ 65,000.00 | PR |
| Daffy Duck | A0004 | CEO | $ 150,000.00 | Finance |
| Miss Piggy | A0005 | Engineer | $ 55,000.00 | Technology |
| Peter Parker | A0006 | Copy | $ 42,000.00 | Mail Room |
| Andrea Blue | A0007 | Teller | $ 35,000.00 | Banking |
| Phil Dumpfy | A0008 | Gofer | $ 48,000.00 | Audit |
| Candy Land | A0009 | Chef | $ 200,000.00 | Kitchen |
| Larry Doyle | A0010 | Editor | $ 22,000.00 | Staffing |
