Solved

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

Posted on 2011-09-12
8
303 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:nassio1985
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Expert Comment

by:GeoffHarper
ID: 36521987
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

0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 350 total points
ID: 36521998
2 things are wrong with your code.
1) In the SperiamoLoop Sub, you have an if. The else clause is Exit Function. This is wrong. The If clause is inside the Sub, not the Function. You have to change it to Exit Sub or (as I think you want to) nothing at all, so it can continue to loop. So, basically, change to exit sub if you want to cancel on the first blank cell, or remove the else/exit lines to just keep looping.
2) A function returns a value. That means that you need to assign it. Your function is defined as string, so you need to either dump it in a string variable, or dump it in a cell. Something like Range(MyCell).OffSet(0,1)=GetFileProperty(myFile, myType)
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 36522011
How do the myFile and myType variables get their values?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:nassio1985
ID: 36522084
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.
0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 350 total points
ID: 36522358
Ok, without knowing how they get their values from column a (seeing as it's two values in one column), try to adapt this into your needs (I will assume data in column A is like-"filename/type":

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
    Dim myFile, myType As String
    Set Rng = Sheets("Sheet1").Range("A2:A4")
    For Each MyCell In Rng
        If MyCell <> "" Then
            myFile = Left(MyCell, InStr(MyCell, "/") - 1)
            myType = Mid(MyCell, InStr(MyCell, "/") + 1)
            MyCell.Offset(0, 1) = GetFileProperty(myFile, myType)
        End If
    Next
End Sub

Open in new window

0
 

Accepted Solution

by:
nassio1985 earned 0 total points
ID: 36522503
It's giving this error "ByRef argument type mismatch"?
0
 

Author Closing Comment

by:nassio1985
ID: 36553425
thanks a lot
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 36522551
myFile is not defined without specifying the type, so is a Variant in SperiamoLoop; while GetFileProperty expects a string.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question