Index Match VBA Error

[Riven]

Hello,

I am trying to make a holiday mailing list in excel which will allow the user to simply place an "X" in a cell to signify the contact should be emailed for a selected holiday. When a holiday is selected, I would like it to read down all the rows for that holiday when a button is pressed and look for an X, then place the name of the individual in a MsgBox (Ultimately this would go to an email list, this is just as an example to help me learn).

The range of names for individuals is listed between A2 and A7, the holidays are between B1 and E1. J5 is the cell where the holiday is entered before pressing the command button The below vba presents me with a runtime error 1004 "Unable to get the Index property of the WorksheetFunction class"

Private Sub CommandButton1_Click()
Dim Result As String
Dim ToList As String
Dim RowNum As Integer
RowNum = 0 'initialize
For n = 1 To Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count 'repeat for every value in column A (contacts)
If Application.WorksheetFunction.Index(Worksheets("Sheet1").Range("B2:E7"), 2 + RowNum, Application.WorksheetFunction.Match(Worksheets("Sheet1").Range("J5"), Worksheets("Sheet1").Range("B1:E1"), 0)) = "x" Then 'check cell value for x
ToList = ToList & " " & Worksheets("Sheet1").Cells(2 + RowNum, 1).Value 'if x add value of name in first column to ToList
ElseIf Application.WorksheetFunction.Index(Worksheets("Sheet1").Range("B2:E7"), 2 + RowNum, Application.WorksheetFunction.Match(Worksheets("Sheet1").Range("J5"), Worksheets("Sheet1").Range("B1:E1"), 0)) = "" Then 'check cell value for blank
ToList = ToList & ""
End If
RowNum = RowNum + 1 'increment to the next row
Next n
MsgBox ToList 'display all values with an x
End Sub

Thank you in advance!

 

[Norie]

Where do you get the error?

What exactly is in J5 and B1:E1?

 

[Riven]

In j5 you would input the holiday you want the list for. B1 to E1 is the list of holidays it is being referenced to for the index. The error ocurs when i click the command button. Thanks for your help.

 

[Norie]

So is the value in J5 a date, is it text, a number?

 

[Riven]

The value in J5 is a string and holds the name of the holiday (Thanksgiving, Christmas, New Years).