VBA Refer to One Column only in a named range?

[ajmckenna]

I have code in with I want to grab cell value of items in 3rd column a 3 column range. I do not want to creat another range name for this Code below.

Thanks!!!!

Sub MasterLastModified()

For Each cell In Range("MasterList").Columns(3) ***HERE does not work***
If cell.Value = "" Then Exit Sub

Dim objFS As New Scripting.FileSystemObject
Dim objFile As Scripting.File
Dim DLM As Date
Dim MasterPath As String
MasterPath = Range("Fileloc") & "Master-Cons\"
Set objFile = objFS.GetFile(MasterPath & cell.Value)
DLM = objFile.DateLastModified

Set objFile = Nothing
Set objFS = Nothing
cell.Offset(0, -1) = DLM
Next
End Sub

 

[Von Pookie]

Using the named range in that way appears to be referring to the entire third column, not each cell in the column.

Try:

Sub MasterLastModified()
Dim objFS As New Scripting.FileSystemObject
Dim objFile As Scripting.File
Dim DLM As Date, MasterPath As String
Dim RowCount As Long, i As Long

With Range("MasterList")
    LastRow = .Columns(3).Rows.Count
    For i = 1 To LastRow
        If .Cells(i, 3).Value = "" Then Exit Sub
        MasterPath = Range("Fileloc") & "Master-Cons\"
        Set objFile = objFS.GetFile(MasterPath & cell.Value)
        DLM = objFile.DateLastModified
        Set objFile = Nothing
        Set objFS = Nothing
        
        .Cells(i, 4).Value = DLM
    Next i
End With

End Sub

 

[ajmckenna]

Thanks for the help but this gives me a

Object required! message when trying to run

 

[Von Pookie]

Admittedly, I couldn't actually test the code as I kept getting errors with all of the FileSystem variables you have. If I comment all of those out, it works. Plus, you haven't mentioned what line is highlighted when the error occurs--I have no idea what to tell you for certain.

One thing I *did* notice, however, was that I goofed up on my variable names in the code I posted. Sorry.

This is a simplified version of the code that is working for me and looping through the cells in the 3rd column of the named range:

Sub MasterLastModified()
Dim RowCount As Long, i As Long

With Range("MasterList")
    RowCount = .Columns(3).Rows.Count
    For i = 1 To RowCount
        If .Cells(i, 3).Value = "" Then Exit Sub
        .Cells(i, 4).Value = "OK"
    Next i
End With

End Sub

You can step through the code by using F8 to see what it's doing.