VBA Refer to One Column only in a named range?

ajmckenna

Board Regular
Joined
Oct 7, 2002
Messages
145
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Using the named range in that way appears to be referring to the entire third column, not each cell in the column.

Try:

Code:
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
 
Upvote 0
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. :oops:

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:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,217,331
Messages
6,135,938
Members
449,973
Latest member
jarzack

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