If Dir(which_file) <> Then --- Send code into loop

espenskeie

Well-known Member
Joined
Mar 30, 2009
Messages
636
Office Version
  1. 2016
Platform
  1. Windows
Hello

I have a problem with this code, when it checks if the file exists, and if it doesn't, the code ends up going in a loop on that For i = 2 etc etc all the time, it cannot move to next i

Does anyone have a suggestion?

Code:
For i = 2 To lr1

    which_file = (Sheets("1HourHL").Range("V2") & "_" & _
    Sheets("1HourDATA").Range("D" & i) & "_" & Sheets("1HourHL").Range("AB2"))
    
      

    which_string = Desktop & "Intraday\Textfiles\" & which_file & ".txt"
            
    highval = Sheets("1HourHL").Range("F" & i).Value
    lowval = Sheets("1HourHL").Range("G" & i).Value
            
If Dir(which_string) <> "" Then

        Workbooks.OpenText Filename:= _
                which_string _
                , Origin:=xlMSDOS, startrow:=1, DataType:=xlDelimited, TextQualifier:= _
                xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
                Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
                Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
                TrailingMinusNumbers:=True

This is the beginning of my code, and heres how it ends:

Code:
If sh3.Range("AC" & i).Value = "Short" Then
                If sh3.Range("Q" & i).Value < sh3.Range("R" & i).Value Then
                    sh3.Range("AD" & i).Formula = "=(N" & i & "-P" & i & ")"
                    sh3.Range("AE" & i).Formula = "=(AD" & i & "/N" & i & ")"
                    sh3.Range("AG" & i).Value = "ExitTimeShort"
                        ElseIf sh3.Range("Q" & i).Value > sh3.Range("R" & i).Value Then '''''' Her slår stoplossen inn
                            sh3.Range("AD" & i).Formula = "=(N" & i & "-R" & i & ")"
                            sh3.Range("AE" & i).Formula = "=(AD" & i & "/N" & i & ")"
                            sh3.Range("AG" & i).Value = "StopShort"
                    End If
            End If
'**************************************************************************************
        
        wb1.Close False
     End If

    Next

So the If Dir.... code ends up at the bottom with just an End If. And the thougt was to go back in line and pull the next i.

Regards
Espen
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I don't see anything from what you're posted.

Are you modifying I in any way? Is there a Do/loop in there somewhere? I believe we'll have to see all of the code.
 
Upvote 0
"So the If Dir.... code ends up at the bottom with just an End If. And the thougt was to go back in line and pull the next i."

You definitely need a "Next" in there somewhere to make it go to the next i. But with only these two snippets of code, it is difficult to understand exactly what you are doing.
 
Upvote 0

Forum statistics

Threads
1,211,435
Messages
6,101,853
Members
447,758
Latest member
jojo3strada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top