Application-Defined or Object-Defined Error in Select Case

Question

Hi guys I'm trying to amend this chunk of vba macro codes which is used to place server type and host name, however when I run it, It shows me run-time error '1004': Application-defined or object-defined error. I'm not that familiar with VBA and I hit a bump when I trying to solve this, hope I can get any advice from you guys thanks.

Update: I have found through debugging that problem relies on hostName = Left(systemName, WorksheetFunction.Find(":KUX", systemName) - 1) , since I have another two host name involved KUL, ithink that's why ststem is confused and it stops at KUL. So now I have to add another case to find hosts with KUL.

Private Sub CommandButton1_Click()
    Dim total_row As Integer
    Dim systemName As String 'Column C
    Dim newString As String
    Dim tabName As String
    Dim hostName As String
    'Dim serverType As String

    'Hostname and Server Type column
    total_row = WorksheetFunction.CountA(Range(Range("C2"), Range("C2").End(xlDown)))
    For i = 2 To total_row + 1
        systemName = Cells(i, 3).Value
        'Copy Size to Size_MB Column
        Cells(i, 6).Value = Cells(i, 5).Value
        'Take Size_MB/1024 to get Size_GB
        Cells(i, 7).Value = Cells(i, 6).Value / 1024
        'Take Size_GB/1024 to get Size_TB
        Cells(i, 8).Value = Cells(i, 7).Value / 1024
        'Tab_Name column S(19)
        tabName = Cells(i, 19).Value
        Select Case tabName
            Case "LINUX"
                hostName = Left(systemName, WorksheetFunction.Find(":LZ", systemName) - 1)
                Cells(i, 2).Value = hostName
                Cells(i, 1).Value = GetServerType(hostName)

            Case "WINDOWS"
                hostName = Mid(systemName, WorksheetFunction.Find("Primary:", systemName) + 8, _
                           WorksheetFunction.Find(":NT", systemName) - WorksheetFunction.Find("Primary:", systemName) - 8)
                Cells(i, 2).Value = hostName
                Cells(i, 1).Value = GetServerType(hostName)
            Case "UNIX"
                hostName = Left(systemName, WorksheetFunction.Find(":KUX", systemName) - 1)
                Cells(i, 2).Value = hostName
                Cells(i, 1).Value = GetServerType(hostName)
            Case Else
                MsgBox "Tab Name don't match"
                Stop
       End Select

       'Column M(13) = Space Used Percent, 'Column O(15) = Greater then 85%
       If (Cells(i, 13).Value > 85) Then
            Cells(i, 15).Value = "Yes"
       Else
            Cells(i, 15).Value = "No"
       End If

       'Column Q(17) = Timestamp, Column R(18) = Date
       Cells(i, 18).Value = Mid(Cells(i, 17), 5, 4)
       'To Generate Month Column p (16)
       Cells(i, 16).Value = GetMonth(Left(Cells(i, 18), 2))

       'Application Name - CEP (Column T)(20)  (Note maybe need check if cannot find the application then display #NA)
       'Note VLOOPUP must minus 3 cause loopup start from column D
       Cells(i, 20).Value = Application.VLookup(hostName, Sheet3.Range("D:V"), 19, False)
       'LOB (BU) - CEP (Column U)(21)
       Cells(i, 21).Value = Application.VLookup(hostName, Sheet3.Range("D:X"), 21, False)
       'System purpose (Column V)(22)
       Cells(i, 22).Value = Application.VLookup(hostName, Sheet3.Range("D:V"), 16, False)
       'Tower (Column W)(23)
       Cells(i, 23).Value = Application.VLookup(hostName, Sheet3.Range("D:Y"), 22, False)
       'Harddisk (Column X)(24) (from column M in MASTERCEP)
       Cells(i, 24).Value = Application.VLookup(hostName, Sheet3.Range("D:V"), 10, False)
       'HW Model (Column Y)(25)
       Cells(i, 25).Value = Application.VLookup(hostName, Sheet3.Range("D:V"), 5, False)
       'MasterBU (Column Z)(26)
       Cells(i, 26).Value = Application.VLookup(Cells(i, 21).Value, Worksheets("MASTERBU").Range("A:B"), 2, False)
       'Ts/Others (Column AC)(29)
       Cells(i, 29).Value = Application.VLookup(hostName, Sheet2.Range("A:B"), 2, False)
       'SL (Column AD)(30)
       Cells(i, 30).Value = Application.VLookup(hostName, Sheet3.Range("D:W"), 20, False)

       'Mount_Check (Column AE)(31)
       'Note: Cells(i,4) is the Mount_Point Column
       'Index Match
       If (Application.WorksheetFunction.IsNA(Application.VLookup(Cells(i, 2), Worksheets("IndexMatch").Range("B:B"), 1, False))) Then
             Cells(i, 31).Value = "#N/A"
       Else
            Cells(i, 31).Value = Application.Index(Worksheets("IndexMatch").Range("G:G"), Application.Match(Cells(i, 4), Worksheets("IndexMatch").Range("D:D"), 0))
       End If

    Next i  
End Sub

Function GetServerType(host_name As String) As String

    Select Case Left(host_name, 1)
        Case "A", "a", "p"
            GetServerType = "AIX"
        Case "S", "s"
            GetServerType = "SUN"
        Case "X", "x", "W", "w", "P"
            GetServerType = "WINTEL"
        Case Else
            GetServerType = ""
    End Select
End Function

Function GetMonth(twoDigitMonth As String) As String
    Select Case twoDigitMonth
        Case "01"
            GetMonth = "Jan"
        Case "02"
            GetMonth = "Feb"
        Case "03"
            GetMonth = "Mar"
        Case "04"
            GetMonth = "Apr"
        Case "05"
            GetMonth = "May"
        Case "06"
            GetMonth = "Jun"
        Case "07"
            GetMonth = "Jul"
        Case "08"
            GetMonth = "Aug"
        Case "9"
            GetMonth = "Sep"
        Case "10"
            GetMonth = "Oct"
        Case "11"
            GetMonth = "Nov"
        Case "12"
            GetMonth = "Dec"
    End Select
End Function

Answer 1

You are going to have a hard time getting debug information out of a failed worksheet function so try an alternate route.

Replace that case "UNIX" statement with the following.

    Case "UNIX"
        If CBool(InStr(1, systemName, ":KUX", vbTextCompare)) Then
            hostName = Left(systemName, InStr(1, systemName, ":KUX", vbTextCompare) - 1)
        ElseIf CBool(InStr(1, systemName, ":KUL", vbTextCompare)) Then
            hostName = Left(systemName, InStr(1, systemName, ":KUL", vbTextCompare) - 1)
        Else
            hostName = vbNullString
            Debug.Print systemName
        End If
        Cells(i, 2).Value = hostName
        Cells(i, 1).Value = GetServerType(hostName)

If it runs through skipping an entry or crashes halfway you will be able to check the VBE's Immediate window (Ctrl+G) for the offending value in column C.