VBA Using If Not for a Split Array

[Lres81715]

I'm trying to find an easier way to approach this task. I currently have a method that works but I'm trying to simplify things for editing.

We have a Team of Employees
TA = Split(employeenames,",")

I want to create a report exclusively for them. I have a way of filtering them out but it's cumbersome and now that I have two dozen reports I'm in charge of, I want to be able to make changes/edits easier. Here is my thought but it doesn't work but I don't know how to go about doing it. Out of my current skill range.

Sub TeamArray()

    Dim TA()        As String
    Dim iRow        As Long
    Dim jRow        As Long


    TA = Split("Employee1,Employee13,Employee16,Employee11,Employee9,Employee8,Employee45,Employee46,Employee47,Employee48,Employee51", ",")
    LastRow = Sheets("Order Detail Report (Table Vers").Cells.find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row


    For iRow = LastRow To 2 Step -1
        For jRow = LBound(TA) To UBound(TA)
            If Not ((Cells(iRow, "A").Value = TA(jRow))) Then
                Cells(iRow, "A").Offset(0, 1) = "#N/A"
            End If
        Next jRow
'        If Not ((Cells(iRow, "A").Value = "Employee1") Or (Cells(iRow, "A").Value = Employee13") _
'                Or (Cells(iRow, "A").Value = "Employee16") Or (Cells(iRow, "A").Value = "Employee11") _
'                Or (Cells(iRow, "A").Value = "Employee9") Or (Cells(iRow, "A").Value = "Employee8") _
'                Or (Cells(iRow, "A").Value = "Employee45") Or (Cells(iRow, "A").Value = "Employee46") _
'                Or (Cells(iRow, "A").Value = "Employee47") Or (Cells(iRow, "A").Value = "Employee48") _
'                Or (Cells(iRow, "A").Value = "Employee51")) Then
'                    Cells(iRow, "A").Offset(0, 1) = "#N/A"
'        End If
    Next iRow


    On Error Resume Next
        Columns("A").Offset(0, 1).SpecialCells(xlConstants, xlErrors).EntireRow.Delete 'Deletion's All employees from report not on Team process.


End Sub

Note: the commented out section is the working portion in my current report. I'm trying to mimic the results but using an array/split method. Not getting the same results because Not ((Cells(iRow, "A").Value = TA(jRow))) isn't treated the same as my old bit of code.

Any help to steer me in the right direction would be appreciated. Thanks!

 

[Lres81715]

Bump,

Still haven't found a solution to this problem.

Another way to ask this question is... "How can I filter/delete columns using an Array of data". I might be approaching this situation wrong

 

[Kenneth Hobson]

It is hard to see what you mean. Did you want to create an Excel file for each or a PDF file or each or?

Here is an example where I used Filter to create a PDF pages for each person. It can easily be modified to create one PDF file for each. One can even add the PDF file or Excel file and an email.

It is easier to show this reference as it includes an attachment while this forum does not. For this forum, you can still share files by links to sites like dropbox.
Print Pdf using Loop condition -- Problem

 

[Lres81715]

visually this is what I'm doing.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Employee Names[/TD]
[TD]Client[/TD]
[TD]Fee[/TD]
[TD]Service Type[/TD]
[TD]etc...[/TD]
[/TR]
[TR]
[TD]Employee1[/TD]
[TD]Client2[/TD]
[TD]550[/TD]
[TD]Appraisal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee105[/TD]
[TD]Client3[/TD]
[TD]550[/TD]
[TD]Appraisal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee9[/TD]
[TD]Client3[/TD]
[TD]550[/TD]
[TD]Appraisal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee209[/TD]
[TD]Client5[/TD]
[TD]600[/TD]
[TD]Appraisal Ext[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee201[/TD]
[TD]Client5[/TD]
[TD]550[/TD]
[TD]Appraisal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee11[/TD]
[TD]Client3[/TD]
[TD]945[/TD]
[TD]Appraisal Rural[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee11[/TD]
[TD]Client3[/TD]
[TD]945[/TD]
[TD]Appraisal Rural[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee209[/TD]
[TD]Client5[/TD]
[TD]600[/TD]
[TD]Appraisal Ext[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The above is a snippet of the original document. I have a report with 25+ Macros embedded that perform varied actions based on they report the user needs. Half of which TEAMS employees only need to show up on that report. Using the Original Macro I commented out above, the spreadsheet of data would only show the following after it was finished filtering/deleting out the employees not needed on their report.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Employee Names[/TD]
[TD]Client[/TD]
[TD]Fee[/TD]
[TD]Service Type[/TD]
[TD]etc...[/TD]
[/TR]
[TR]
[TD]Employee1[/TD]
[TD]Client2[/TD]
[TD]550[/TD]
[TD]Appraisal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee9[/TD]
[TD]Client3[/TD]
[TD]550[/TD]
[TD]Appraisal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee11[/TD]
[TD]Client3[/TD]
[TD]945[/TD]
[TD]Appraisal Rural[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee11[/TD]
[TD]Client3[/TD]
[TD]945[/TD]
[TD]Appraisal Rural[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm limited in my VBA skill-set so I only know how to use the commented out section to filter out by reverse individual employees (all members that should be on the report, the rest removed). This works fine by when employees come and go, teams shift and change, I have to change all Macros that have these employees. Would rather just do it once using an array/split if its possible to use it in the way I want. I'm thinking maybe I can use it as a countif function, if TRUE then Flag OK. or something along these lines. But I lack the skills to make it happen.