1004 error on Array. Worksheet failed.

[noslenwerd]

First off thanks to everyone on this forum for your help. I have learned a ton and am able to create a process for my org that is going to save everyone a ton of hours. I will be excited to share with everyone once it is done.

So the code below is supposed to reference a named range "_FileLoc" (all values in one column). That column holds values that point to a file ie: "C:\DSSworksheet\review-content\conversion\mobileresponsive.html"

It then will check if it exists, and pop a msgbox up saying if it does/doesn't exist. But I am getting the 1004 error on the bolded line below. Any ideas?

Sub CheckHTMLFile()

    Dim RevRow, FinalCol, ReviewLoc, dm, TopicCount  As Long
    Dim TopicNamedRange, TopicFolder As String
    Dim cel, fleArray As Range
    Dim shtC As Worksheet

    Set shtC = Sheets("NamedR")

    With shtC
        TopicCount = WorksheetFunction.CountA(Range("_FileLoc"))
        Set fleArray = .Range("_FileLoc")
    End With

        dm = 1
        For dm = 1 To TopicCount
            RevRow = 1
            TopicNamedRange = fleArray(dm)
                For Each cel In shtC.Range(TopicNamedRange) '*************This is where the 1004 error happens***************************
                    If shtC.Range(TopicNamedRange).Cells(RevRow, 1).Value <> "" Then
                        TopicFolder = Range(TopicNamedRange).Cells(RevRow, 1).Value
                            If Dir(TopicFolder) <> "" Then
                                MsgBox Range(TopicNamedRange).Cells(RevRow, 1).Offset(0, 2).Value & " DOES exist at " & TopicFolder
                            Else
                                MsgBox Range(TopicNamedRange).Cells(RevRow, 1).Offset(0, 2).Value & " doesn't exist at " & TopicFolder
                            End If
                        RevRow = RevRow + 1
                    Else
                        Exit Sub
                    End If
               Next cel
        Next dm
End Sub

 

[JLGWhiz]

From what I can pick out of the code, I would say that at the point where the error is generated, 'TopicNamedRange' variable is probably empty. You have 'fileArray' declared as 'Range' instead of variant, but are attempting to use it as an array. The 'Set' statement to initialize fileArray would not set up an array of cell values, if that is what you intended. You would need to add .Value property to the statement and remove the 'Set', while declaring fileArray as Variant. Then it might create the array you are looking for. But I could have read this wrong.