• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

VBA loop wipe 2 columns

Hello everyone,
thanks to some of you I finally have my macro working, but I still have an issue when I run it it wipe out all the contents from B2,B3 and C2, C3 D2, D3 E2, E3, anyone knows why?
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")

    If oFS.FileExists(myFile) Then
        Select Case UCase(Trim(myType))
            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
    Else
        GetFileProperty = "File does not exist"
    End If
End Function

Public Sub SperiamoLoop()
    Dim MyCell As Variant, Rng As Range
    Dim myFile As String, myType As String
    Set Rng = Sheets("Sheet1").Range("A2:A4")
    For Each MyCell In Rng
        myFile = MyCell.Value
        If myFile <> "" Then
            For i = 1 To 4
                myType = Sheets("Sheet1").Range("A1").Offset(0, i)
                MyCell.Offset(0, i) = GetFileProperty(myFile, myType)
            Next
        End If
    Next
End Sub

Open in new window

z1.jpg
0
nassio1985
Asked:
nassio1985
  • 4
  • 3
  • 2
4 Solutions
 
Farzad AkbarnejadDeveloperCommented:
Hello,
Make sure you correctly copy formula into cells.
Select B1 then copy it to B2, B3. If you click on B2 you must see your formula referenced to B2 and so on...

-FA
0
 
nassio1985Author Commented:
I've done this but every time I make the loop run it wipe the cell as you see in the screen shot.
0
 
Farzad AkbarnejadDeveloperCommented:
Hello,
Select B1. Then Drag its right-down corner to B2, B3

or

Use Edit > Fill > Down command from menu.

-FA
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
TommySzalapskiCommented:
It didn't wipe the columns, it's just running the else every time again and you commented it out.

For your code to work as written, the first row MUST match your cases.
Make row 1:
Filename CREATED MODIFIED ACCESSED SIZE
Then DO NOT change it and the code will work.
0
 
TommySzalapskiCommented:
If you want the columns named differently or arranged differently, then just make sure the column name matches the name in the CASE line.

If you want that data in different columns, then change this line
 For i = 1 To 4
If you change it to
 For i = 3 To 6
then it will use columns D:G instead of B:E
0
 
nassio1985Author Commented:
How could e running the else if like you said I commented it out, by the way I tried without comment and as you can see from the screen shot that's what I get, the first row does match my case as you said and I did it even before. How could I avoid the else to run again and again?
without-comments.jpg
0
 
TommySzalapskiCommented:
No, the text in the first row must exactly match the text in the cases. So B1 must contain the text CREATE spelled exactly the way that it is spelled in the case.
0
 
nassio1985Author Commented:
Hum... If I well understood this is what I sould put inside the B1 =GetFileProperty(A1,"CREATED"), if so it's not working....
0
 
TommySzalapskiCommented:
No. In B1 just put CREATED
That's it. Just the text.
No = or formula or anything.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now