VBA - Countif, Index and Match

VBA_count.example.xlsx

Sheet attached above.

Hello,
One of my assistant managers runs several reports from our internal system to track several different things. The reports are distributed to their staff and as each employee completes the file, they place their initials in a specified column. I threw a macro in the mix to allow my AM to keep a count of how many files each employee claimed on each report. The macro adds a sheet, adds each employee's name and throws in the total of times their initials appear in the specified colum. This is part of a larger function which will merge this data from all the reports in a specified folder.

This has been working fine for a majority of the reports, as most of the reports have the same heading, e.g. "Priority". I run in to issues when the specified column may be labeled something different, e.g. "First Class". I am trying to have the code return a count if the heading is any of the specified headings. As I'm really only 5 1/2 months in to working with VBA, I'm sure there is a more efficient way to do what I'm attempting, which is fine, but the main thing I need to accomplish is to get a count based off of more than one specified heading, i.e. Priority or First Class or Freight, etc.

the reason I did not just have it count "everything in column I" was because each report varies as to where the specified column is, based on the neccesary information

Code is below:

Sheets.Add After:=Sheets(Sheets.Count)
ActiveCell.FormulaR1C1 = "NAME"
Range("B1").Select
ActiveCell.FormulaR1C1 = "TOTAL"
Range("A2").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Janice Fargo"
Range("A3").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Summer Fitzgerald"
Range("A4").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Sam Levy"
Range("A5").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Mick Belmont"
Range("A6").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Sarah Holcomb"
Range("A7").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Alvin Ballantine"
Range("A8").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Shaundra Robbins"
Range("A9").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Andrew Jenkins"
Range("B9").Select
Columns("A:A").EntireColumn.AutoFit
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(INDEX(Sheet1!R[-1]:R[65534],0,MATCH(""Priority"",Sheet1!R[3]C[-1]:R[3]C[76],0)),""JAF"")"
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(INDEX(Sheet1!R[-2]:R[65533],0,MATCH(""Priority"",Sheet1!R[2]C[-1]:R[2]C[76],0)),""SF"")"
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(INDEX(Sheet1!R[-3]:R[65532],0,MATCH(""Priority"",Sheet1!R[1]C[-1]:R[1]C[76],0)),""SL"")"
Range("B5").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(INDEX(Sheet1!R[-4]:R[65531],0,MATCH(""Priority"",Sheet1!RC[-1]:RC[76],0)),""MB"")"
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(INDEX(Sheet1!R[-5]:R[65530],0,MATCH(""Priority"",Sheet1!R[-1]C[-1]:R[-1]C[76],0)),""SH"")"
Range("B7").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(INDEX(Sheet1!R[-6]:R[65529],0,MATCH(""Priority"",Sheet1!R[-2]C[-1]:R[-2]C[76],0)),""AB"")"
Range("B8").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(INDEX(Sheet1!R[-7]:R[65528],0,MATCH(""Priority"",Sheet1!R[-3]C[-1]:R[-3]C[76],0)),""SR"")"
Range("B9").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(INDEX(Sheet1!R[-8]:R[65527],0,MATCH(""Priority"",Sheet1!R[-4]C[-1]:R[-4]C[76],0)),""AJ"")"
Range("B10").Select
End Sub

Probably too simplistic approach, but why don't you just create a Pivot Table?

Just select all the data from the header row down, go to Insert | Pivot Table, drag the Initials (Priority) to the row labels and the File # to the values.

You could, if you wished, use the existing user list as a VLOOKUP Table.


Regards, TMS

Alpha Frog - that almost worked for what I need it for, however, it does not update the count as the sheet is added to (we run that code before we release the sheet each day). It could still work, for sure, but it still throws in that one extra step I'm trying to avoid., especially since I can't delete sheets in a shared workbook.

TMShucks...yeah, you're probably right. I've been avoiding trying to dive in to how to create a pivot table in VBA, but it may be one of those things I have to invest in, and will probably be better for me in the end.

My endgame, so to speak, is to merge all of this data at the end of every day. There are appx 30 of these daily reports that end up in one daily folder, and I'm using the Ron de Bruin's Add in to merge everything at the end of each day...there's probably a more efficient way of collecting that data, but within my limited realm, there's not...yet.