For/Next Resulting In "Variable uses an AUtomation type not supported in Visual Basic" Error

[Ark68]

Please consider this code. The line highted in red is giving me an error. :: "Variable uses an Automation type not supported in Visual Basic". Is anyone able to shed some light on what's wrong and what I need to do to resolve the problem. It was working before I nested it in the If dstart > 0 Then test. dstart = 13, dend = 28.

Sub pop_staff()
    Stop
    Dim ws_staffdest As Worksheet
    Dim ArrCrew As Variant
    Dim sname As String, etime As String, stime As String, ws As String
    Dim ws_ssheet As Worksheet 'sourcesheet
    Dim drow As Long, g As Long, h As Long
    Dim LkFor As Variant
    Dim dstart As Long, dend As Long, cntdia As Long
   
    Set ws_staffdest = wb_dia.Worksheets("Staff")
   
    ArrCrew = Array("LSP", "CRP", "CWP", "CUE1", "CUE2", "CUL", "BPE", "BPL", "HPE", "HPL", "RPE", "RPL", "WPE", "WPL", "LWP", "EVE", "EVL")
   
   
    With ws_staffdest
        For x = LBound(ArrCrew) To UBound(ArrCrew)
            sname = ArrCrew(x)
            ws = sname
            Set ws_ssheet = wb_data.Worksheets(sname)
            If ws_ssheet.Tab.ColorIndex = xlColorIndexNone Then
                drow = Application.WorksheetFunction.Match(sname, ws_staffdest.Columns(3), 0)
                .Cells(drow, 4) = ws_ssheet.Range("M4")
                .Cells(drow, 5) = Application.WorksheetFunction.VLookup(ws_ssheet.Range("M4"), ws_lists.Range("P:Q"), 2, False)
                g = Len(ws_ssheet.Range("M5"))
                h = InStr(ws_ssheet.Range("M5"), "-")
                stime = Left(ws_ssheet.Range("M5"), h - 2)
                etime = Right(ws_ssheet.Range("M5"), h - 2)
                .Cells(drow, 6) = stime
                .Cells(drow, 7) = etime
                pdaStart = 13
                pdaEnd = Application.WorksheetFunction.Match("Facility Maintenance Activities", ws_ssheet.Columns(1), 0) - 2
                Set rngPDA2 = ws_ssheet.Range("A" & pdaStart & ":R" & pdaEnd)
            'Diamonds
                dstart = 0
                dend = 0
                cntdia = 0
                LkFor = "D"
                FirstAndLastRows ws, LkFor, dstart, dend
                'count how many cells aren't shaded
                If dstart > 0 Then
                    For j = dstart To dend
                        If ws_ssheet.Cells(j, 10).Interior.ColorIndex = xlNone Then cntdia = cntdia + 1
                    Next j
                End If
                .Cells(drow, 9) = cntdia
            'Fields
                dstart = 0
                dend = 0
                cntfld = 0
                LkFor = "F"
                FirstAndLastRows ws, LkFor, dstart, dend
                'count how many cells aren't shaded
                If dstart > 0 Then
                    For j = dstart To dend
                        If ws_ssheet.Cells(j, 10).Interior.ColorIndex = xlNone Then cntfld = cntfld + 1
                    Next j
                End If
                .Cells(drow, 10) = cntfld
            'Courts
                cntcrt = 0
                LkFor = "C"
                FirstAndLastRows ws, LkFor, dstart, dend
                'count how many cells aren't shaded
                If dstart > 0 Then
                    For j = dstart To dend
                        If ws_ssheet.Cells(j, 10).Interior.ColorIndex = xlNone Then cntcrt = cntcrt + 1
                    Next j
                End If
                .Cells(drow, 11) = cntcrt
            'Trails
                cnttrl = 0
                LkFor = "T"
                FirstAndLastRows ws, LkFor, dstart, dend
                'count how many cells aren't shaded
                If dstart > 0 Then
                    For j = dstart To dend
                        If ws_ssheet.Cells(j, 10).Interior.ColorIndex = xlNone Then cnttrl = cnttrl + 1
                    Next j
                End If
                .Cells(drow, 12) = cnttrl
            'Passive
                cntpsv = 0
                LkFor = "P"
                FirstAndLastRows ws, LkFor, dstart, dend
                'count how many cells aren't shaded
                If dstart > 0 Then
                    For j = dstart To dend
                        If ws_ssheet.Cells(j, 10).Interior.ColorIndex = xlNone Then cntpsv = cntpsv + 1
                    Next j
                End If
                .Cells(drow, 13) = cntpsv
               
                cntbkg = cntdia + cntfld + cntcrt + cnttrl + cntpsv
                .Cells(drow, 13) = cntpsv
               
            End If
        Next x
    End With
   
End Sub

 

[Domenic]

First, make sure that all your variables are declared.

Then add Option Explicit at the very top of your module. This will make sure that all your variables have been declared, and catch any spelling errors.

Then make sure that the variables you pass to FirstAndLastRows() match the data type specified by it's parameters.

Then try running your code again.

 

[Ark68]

Hi Domenic. Thank you.
I declared j as long, ans was still getting the error. I changed the variable from j to lpj and it worked. VB didn't like J?

 

[Domenic]

Normally, there shouldn't be a problem with using j as a variable. It probably has something to do with FirstAndLastRows().

Anyway, glad you've sorted it out.

Cheers!