Link to home
Start Free TrialLog in
Avatar of nassio1985
nassio1985

asked on

How to make this function loop throght column A in a excel spread sheet

Hello everyone ,
I trying to call this function inside a loop but I cannot make it works, basically what I 'd like to do is call this function for every cell in the selected range. I have attached the code please have look.
May anyone help me please?
Thanks in advance
Public Function GetFileProperty(myFile As String, myType As String) As String

    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Select Case UCase(Trim(myFile))
        Case "CREATED"
            GetFileProperty = oFS.GetFile(myFile).DateCreated
        Case "MODIFIED"
            GetFileProperty = oFS.GetFile(myFile).DateLastModified
        Case "ACCESSED"
            GetFileProperty = oFS.GetFile(myFile).DateLastAccessed
        Case "SIZE"
            GetFileProperty = oFS.GetFile(myFile).Size
        Case Else
            GetFileProperty = "txt"
    End Select
End Function



 

Public Sub SperiamoLoop()

    Dim MyCell As Variant, Rng As Range
    Set Rng = Sheets("Sheet1").Range("A2:A4") '''''sets the range to use
    For Each MyCell In Rng ''''checks each cell in range
        If MyCell <> "" Then '''''will only do something if the cell is not blank
            Call GetFileProperty(myFile, myType) '''Put your code here
        Else '''''if cell is equal to blank
            Exit Function ''''then quit macro
        End If
    Next
End Sub

Open in new window

Avatar of GeoffHarper
GeoffHarper
Flag of United States of America image

First off, I see that it will not loop through all the cells in range if any are blank; it will stop at the first blank one.  I.e. you need to do away with the ELSE.
If MyCell <> "" Then '''''will only do something if the cell is not blank
            Call GetFileProperty(myFile, myType) '''Put your code here
        End If

Open in new window

SOLUTION
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GrahamSkan
How do the myFile and myType variables get their values?
Avatar of nassio1985
nassio1985

ASKER

TO GrahamSkan:
 myFile and mtType get their values from the spread sheet column A.

TO Cluskitt:
Sorry but let's say that I'm a newbie so could please be more specific, how shall I change the code?

To GeoffHarper:
I know that if one cell in the loop in blanck it will skip it, I have done to put an error check, but the range it's a list made by myself so there will not  any black cells.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks a lot
myFile is not defined without specifying the type, so is a Variant in SperiamoLoop; while GetFileProperty expects a string.