I need to loop through column A matching the ActiveCell.Row to a declared Array and storing to a variable. Is there as simpler way to loop this code, a sample of what I have follows.
Range("A65533").End(xlUp).Select
LRow = ActiveCell.Row
Cells(1, 1).Select
Do Until ActiveCell.Row = LRow + 1
If ActiveCell = Empty Then
ActiveCell.Offset(1, 0).Select
Else
If ActiveCell = 49000 Then
Dep49 = ActiveCell.Row
ElseIf ActiveCell = 41000 Then
Dep41 = ActiveCell.Row
ElseIf ActiveCell = 42000 Then
Dep42 = ActiveCell.Row
ElseIf ActiveCell = 43000 Then
Dep43 = ActiveCell.Row
ElseIf (ActiveCell <> "49000") Or (ActiveCell <> "41000") Or (ActiveCell <> "42000") Then
Msg = "There was an error in the Department Code. Check all codes in Column 'A'. This routine has been cancelled!"
MsgBox Msg, , "Department Code Check"
Exit Sub
End If
ActiveCell.Offset(1, 0).Select
End If
Loop
Range("A65533").End(xlUp).Select
LRow = ActiveCell.Row
Cells(1, 1).Select
Do Until ActiveCell.Row = LRow + 1
If ActiveCell = Empty Then
ActiveCell.Offset(1, 0).Select
Else
If ActiveCell = 49000 Then
Dep49 = ActiveCell.Row
ElseIf ActiveCell = 41000 Then
Dep41 = ActiveCell.Row
ElseIf ActiveCell = 42000 Then
Dep42 = ActiveCell.Row
ElseIf ActiveCell = 43000 Then
Dep43 = ActiveCell.Row
ElseIf (ActiveCell <> "49000") Or (ActiveCell <> "41000") Or (ActiveCell <> "42000") Then
Msg = "There was an error in the Department Code. Check all codes in Column 'A'. This routine has been cancelled!"
MsgBox Msg, , "Department Code Check"
Exit Sub
End If
ActiveCell.Offset(1, 0).Select
End If
Loop