CODEWITHSUNDEEP
excelvbaloopscounter
Jackson5
Jackson5

Reputation: 65

Excel VBA For Loop and Nested IF statements for Counter

I have a value in a cell that should match the filename of a document in a directory.

Sheet3 Column A1 = C:\Users\Admin\Desktop\Folder1

Sheet3 Column A2 = test.xls 

‘Location of directory
sCurrentXLDirectory = Worksheets("Sheet3").Cells(1, 1).Value
Set CurrentXLFSO = CreateObject("Scripting.FileSystemObject")
ProceedNow = True
Set CurrentXLFolder = CurrentXLFSO.GetFolder(sCurrentXLDirectory)
Set CurrentXLFiles = CurrentXLFolder.Files
‘Always 10 files in this folder
If CurrentXLFiles.Count <> 10 Then
   MsgBox "Wrong Directory or Folder Mismatch"
   ProceedNow = False
Else
'Return one for indentical filename
Dim NameCount As Integer
NameCount = 0
For Each folderIDX In CurrentXLFiles
 ‘Compare file names specified cell value
    If folderIDX.Name = Worksheets("Sheet3").Cells(1, 2).Value Then
    NameCount = NameCount + 1
        If NameCount <> 1 Then
            MsgBox "Unable to find file”
            ProceedNow = False
        End If
    End If
Next
End If

For some reason, even if I change test.xls to test1.xls, it will still do Proceed = True

If a nested IF statement is not the preferable way to do this, please guide me in the right direction.

Upvotes: 0

Views: 1060

Answers (2)

n8.
n8.

Reputation: 1738

The code you provided will not change a file name, so maybe this is just the beginnings of your attempt. What I found, though, is that Range("A2") is "Cells(2, 1)", not "Cells(1, 2)", as you currently have it. You are referencing cell B1, which probably does not contain a file name.

To alleviate such confusion in the future, always refer to one or the other, then such problems are avoided or easily diagnosed.

Such as:

If folderIDX.Name = Worksheets("Sheet3").Range("A2").Value Then

This should trip that "ProceedNow = False" flag that you are looking for.

Upvotes: 0

Dustin
Dustin

Reputation: 411

If the purpose of the procedure is verify if a file exists or does not exist, using the Dir() function would be much simpler.

If this is the goal, try the following code:

Sub test()
    Dim sDirectory As String
    Dim sFile As String

    sDirectory = Worksheets("Sheet3").Cells(1, 1).Value
    sFile = Worksheets("Sheet3").Cells(1, 2).Value
    sFile = Dir(sDirectory & "\" & sFile, vbDirectory)
    If Len(sFile) = 0 Then
        MsgBox "Unable to find file"
    End If
End Sub

Upvotes: 2

Related Questions