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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How do the myFile and myType variables get their values?
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks a lot
myFile is not defined without specifying the type, so is a Variant in SperiamoLoop; while GetFileProperty expects a string.
Open in new window