CODEWITHSUNDEEP
vbaexcel
TroyPilewski
TroyPilewski

Reputation: 369

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"`

Upvotes: 0

Views: 2864

Answers (1)

Jon Crowell
Jon Crowell

Reputation: 22358

You have the wrong syntax for your range.

Change this:

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

to this:

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

One other thing -- you should use .value or .value2 instead of .text unless you have a very specific reason for using .text. See Charles Williams' article for an excellent analysis of the three properties: TEXT vs VALUE vs VALUE2 - Slow TEXT and how to avoid it.

Upvotes: 2

Related Questions