1004 error on Array. Worksheet failed.

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
85
Office Version
  1. 365
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?

VBA Code:
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top