CODEWITHSUNDEEP
excelvba
Yvain
Yvain

Reputation: 291

Looping through files in a folder, code breaks if statement is false a singular time

I'm working on an excel macro to loop through files in a folder, I'm looping through files looking for a particular string in the file name, and if it finds a file with that string in its name, it sets a certain cell value to 1.

However, it seems as though after it doesn't find the file one time, it will never find another one again, and will never set another cell to 1. I have checked the test directory I'm using manually to ensure that it's not actually the case that no files after that exist.

Here's what I have:

'loop through files in folder checking if value in B column is present


Dim MyObj As Object, MySource As Object, file As Variant
file = Dir("dummydirectory")



Dim rng As Range, cell As Range
Set rng = Range("B164:B1533")
Dim targetcell As Range


For Each cell In rng
 Do While (file <> "")
   Set targetcell = cell.Offset(0, 12) 'sets targetcell 12 columns to the right of the original (B -> N)
      If InStr(file, cell.Value) > 0 Then
         targetcell.Value = 1
         Exit Do
      End If
     file = Dir
  Loop
  
Next cell
End Sub

'current issues:
'as soon as the the if statement is false once, it will never set another cell to 1
'ie: as soon as one cell is not set to 1, it stops working essentially

I thought maybe it had something to do with where file = Dir was placed, I put it outside the do loop, in the if statement, and nothing seems to work.

Your help would be much appreciated!

Upvotes: 0

Views: 33

Answers (1)

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

You need to reset file after each Loop. The following is a more efficient way of doing that:

For Each cell In rng
 file = Dir("dummydirectory")
 Do 
   Set targetcell = cell.Offset(0, 12) 'sets targetcell 12 columns to the right of the original (B -> N)
      If InStr(file, cell.Value) > 0 Then
         targetcell.Value = 1
         Exit Do
      End If
     file = Dir
  While (file <> "")
  
Next cell

Upvotes: 1

Related Questions