Index Match VBA Error

Riven

New Member
Joined
May 31, 2015
Messages
4
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!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Where do you get the error?

What exactly is in J5 and B1:E1?
 

Riven

New Member
Joined
May 31, 2015
Messages
4
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
So is the value in J5 a date, is it text, a number?
 

Riven

New Member
Joined
May 31, 2015
Messages
4
The value in J5 is a string and holds the name of the holiday (Thanksgiving, Christmas, New Years).
 

Watch MrExcel Video

Forum statistics

Threads
1,123,083
Messages
5,599,643
Members
414,326
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top