I have a private sub that I call at the end of my code. It's purpose is to delete any individuals whose SSN match a running array. I have two sheets that it searches through and the second one works great, however the first doesn't seem to be working.
I attempted to debug it and it is finding the right numbers the one individual that I am concerned with matches the array, however the If statement doesn't seem to effect it. Here is what I have, any and all help would be appreciated. Again the issue is that the first If statement isn't working properly. I have obviously changed the SSN's for security reasons. Thanks again!
Private Sub Delete_Deceased()
Dim tw As Workbook: Set tw = ThisWorkbook
Dim visa As Worksheet: Set visa = tw.Worksheets("Visa")
Dim nav As Worksheet: Set nav = tw.Worksheets("Navigator")
Dim rwCnt_visa As Long: rwCnt_visa = visa.Cells(Rows.Count, 1).End(xlUp).Row
Dim rwCnt_nav As Long: rwCnt_nav = nav.Cells(Rows.Count, 1).End(xlUp).Row
Dim x As Long
Dim y As Long
Dim Deceased() As Variant
Deceased = Array(123456789, 234567890, 345678901, 456789012, 567890123, 678901234, _
789012345, 890123456, 901234567)
Dim visa_Inx As Long
Dim nav_Inx As Long
visa.Columns("P:P").NumberFormat = "@" 'Format the VISA SSN column as text
For x = rwCnt_visa To 4 Step -1
For visa_Inx = LBound(Deceased) To UBound(Deceased)
If visa.Cells(x, 16).Value2 = Deceased(visa_Inx) Then
Debug.Print "Row: " & x
'visa.Rows(x).Delete shift:=xlShiftUp
End If
Next visa_Inx
Next x
For y = rwCnt_nav To 2 Step -1
For nav_Inx = LBound(Deceased) To UBound(Deceased)
If nav.Cells(y, 5).Value2 = Deceased(nav_Inx) Then
nav.Rows(y).Delete shift:=xlShiftUp
End If
Next nav_Inx
Next y
End Sub
Aucun commentaire:
Enregistrer un commentaire