Application or Object defined error - VBA Excel 2013

Application or Object defined error - VBA Excel 2013



I want code to check one column of data for a condition ie: Range

Qualification. If they are required to go the the Range the value will be

"REQ" if they are not the values will be "E", "S", "M", and "NR". I use

[select case] to check the condition. At the start of the select case I

get this error.

I am not sure if I am making the sell reference right or not. After the

array is populated with names from another column, I then go through and

remove the empty elements from the array and then display all elements of

the array in a msgbox. Below is the code I used:

'Declares total number of personnel as integer

Dim total As Integer

total = Worksheets("MASTER").Range("C4").Value

'Declares single element array with personnel full names

ReDim names(total) As String

'Loops through the array checking to see if personnel have qualified on

the Rifle Range

For i = (1 + 6) To (total + 6)

Select Case Worksheets("MASTER").Range(Cells(i, 23)).Text

Case "REQ"

names(i - 6) = Worksheets("MASTER").Range(Cells(i, 7)).Value

Case "NR"

names(i - 6) = vbNullString

Case "E"

names(i - 6) = vbNullString

Case "S"

names(i - 6) = vbNullString

Case "M"

names(i - 6) = vbNullString

End Select

Next

'Declares a new array to remove blank elements from the orignal array

ReDim msgnames(LBound(names) To UBound(names))

'Loops through new array removing empty elements

For i = LBound(names) To UBound(names)

If names(i) <> vbNullString Then

x = x + 1

msgnames(x) = names(i)

End If

Next

'Displays every element of the array

For i = LBound(msgnames) To UBound(msgnames)

msg = msg & msgnames(i) & vbNewLine

Next

'Declares COMP, NOTCOMP, REQ and NOTREQ variables

Dim COMP As String

Dim NOTCOMP As String

Dim REQ As String

Dim NOTREQ As String

'Adds a comment to the bottom of the Message Box

MsgBox msg, vbOKOnly, "Rifle Range"`