Link to home
Start Free TrialLog in
Avatar of bala kumaran
bala kumaran

asked on

DIR issue

myfile = Dir(ippath & "*.xlsm")
myfile1 = Dir(ippath1 & "*.xlsx")
Do While myfile <> ""
Do While myfile1 <> ""
loop
loop
end sub

I used 2 DIR, like the above, my issue was, one finish myfile1 DIR working inside the myfile DIR, it will not get myfile DIR another file, wrongly go to myfile1 DIR
Avatar of Professor J
Professor J

what is your complete code? if possible can you please post your complete sub routine?
Avatar of bala kumaran

ASKER

Public Sub Insert()

Dim myfile As String
Dim myfile1 As Variant
ippath = Sheet1.TextBox1.Text
ippath1 = Sheet1.TextBox2.Text
myfile = Dir(ippath & "*.xlsm")
myfile1 = Dir(ippath1 & "*.xlsx")

Do While myfile <> ""

    Workbooks.Open FileName:=ippath & myfile, UpdateLinks:=True
    Application.DisplayAlerts = False

    Sheets("Minimum Accounts Work Tracker").Select
   
    If Sheet1.OptionButton1.Value = True Then
   
    Rows("4:4").Select
    Cells.Find(What:="low").Activate
    ActiveCell.Offset(0, -2).Select
    ActiveCell = Sheet1.TextBox6.Text
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R[1]C,1),""Sun"",""Mon"",""Tue"",""Wed"",""Thu"",""Fri""),"""")"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Priority!C1:C4,4,0),"""")"
    Selection.Copy
    Addrs = Split(ActiveCell.Address, "$")(1)
    RcNt = ActiveSheet.UsedRange.Rows.Count
    Range(Addrs & "5:" & Addrs & RcNt).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("i5").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    End If
   
           
    If Sheet1.OptionButton3.Value = True Then
         
        Do While myfile1 <> ""
       
    Workbooks.Open FileName:=ippath1 & myfile1, UpdateLinks:=True
    m1 = ActiveWorkbook.Name
    Range("a2:l" & ActiveSheet.UsedRange.Rows.Count).Select
    Selection.Copy
    Windows(myfile).Activate
    Sheets("Priority").Select
    Range("a2").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A:A").Select
    Selection.TextToColumns DataType:=xlDelimited
    Range("a1").Select
    Sheets("Minimum Accounts Work Tracker").Select
    Range("bd4").Select
   
    If ActiveCell.Value = "" Then
    Rows("4:4").Select
    Cells.Find(What:="low").Activate
    ActiveCell.Offset(0, -2).Select
    ActiveCell = Sheet1.TextBox6.Text
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R[1]C,1),""Sun"",""Mon"",""Tue"",""Wed"",""Thu"",""Fri""),"""")"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Priority!C1:C4,4,0),"""")"
    Selection.Copy
    Addrs = Split(ActiveCell.Address, "$")(1)
    RcNt = ActiveSheet.UsedRange.Rows.Count
    Range(Addrs & "5:" & Addrs & RcNt).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Else
   
    Rows("4:4").Select
    Cells.Find(What:="low").Activate
    ActiveCell.Offset(0, -1).Select
    Columns(ActiveCell.Column).Insert
   
    ActiveCell.formula = Split(myfile1, ".")(0)
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R[1]C,1),""Sun"",""Mon"",""Tue"",""Wed"",""Thu"",""Fri""),"""")"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Priority!C1:C4,4,0),"""")"
    Selection.Copy
    Addrs = Split(ActiveCell.Address, "$")(1)
    RcNt = ActiveSheet.UsedRange.Rows.Count
    Range(Addrs & "5:" & Addrs & RcNt).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("i5").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    End If
    Windows(m1).Close
    myfile1 = Dir()
 
   
        Loop
         
    End If
   
       
    If Sheet1.OptionButton2.Value = True Or Sheet1.OptionButton4.Value = True Then
       
    Rows("4:4").Select
    Cells.Find(What:="low").Activate
    ActiveCell.Offset(0, -1).Select
    Columns(ActiveCell.Column).Insert
    ActiveCell = Sheet1.TextBox6.Text
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R[1]C,1),""Sun"",""Mon"",""Tue"",""Wed"",""Thu"",""Fri""),"""")"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Priority!C1:C4,4,0),"""")"
    Selection.Copy
    Addrs = Split(ActiveCell.Address, "$")(1)
    RcNt = ActiveSheet.UsedRange.Rows.Count
    Range(Addrs & "5:" & Addrs & RcNt).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("i5").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    End If
 
   
    Rows("4:4").Select
    Cells.Find(What:="low").Activate
    Addrs1 = Split(ActiveCell.Address, "$")(1)
    ActiveCell.Offset(0, 8).Select
    Addrs2 = Split(ActiveCell.Address, "$")(1)
    RcNt = ActiveSheet.UsedRange.Rows.Count
    ActiveCell.Offset(-3, 0).Select
    Range(ActiveCell, ActiveCell.Offset(0, -8)).Select
    Selection.Copy
    Range(Addrs1 & "5:" & Addrs2 & RcNt).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(0, 4).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns DataType:=xlDelimited
   
    Range("f1").Select
    Selection.Copy
    Range("f5:f" & ActiveSheet.UsedRange.Rows.Count).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.Replace What:="1/0/1900", Replacement:=""
    Range("h1").Select
    Selection.Copy
    Range("h5:h" & ActiveSheet.UsedRange.Rows.Count).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
   
    Rows("4:4").Select
    Cells.Find(What:="low").Activate
    ActiveCell.Offset(1, 9).Select
    Addrs3 = Split(ActiveCell.Address, "$")(1)
    RcNt = ActiveSheet.UsedRange.Rows.Count
    ActiveCell.FormulaR1C1 = "=IF(R[0]C8=""Not Worked"","""",TODAY()-R[0]C8)"
    Selection.Copy
    Range(Addrs3 & "5:" & Addrs3 & RcNt).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("f5").Select
       
    If Sheet1.OptionButton1.Value = True Or Sheet1.OptionButton2.Value = True Then
    Range("d1").Select
    Selection.ClearContents
    Range("G2").Select
    Selection.Copy
    Range("g5:g" & ActiveSheet.UsedRange.Rows.Count).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("f5").Select
    End If
       
    If Sheet1.OptionButton3.Value = True Then
    Range("d1").Select
    ActiveCell = Sheet1.TextBox6.Text
    Range("G2").Select
    Selection.Copy
    Range("g5:g" & ActiveSheet.UsedRange.Rows.Count).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("f5").Select
    End If
   
    If Sheet1.OptionButton4.Value = True Then
    Range("G4").Activate
    Rows("4:4").Select
    Selection.AutoFilter
    ActiveSheet.Range("a4:cz" & ActiveSheet.UsedRange.Rows.Count).AutoFilter Field:=7, Criteria1:=Array( _
        "Cleared", "Required", "Required 2", "="), Operator:=xlFilterValues
    Range("G1").Select
    Selection.Copy
    Range("g5:g" & ActiveSheet.UsedRange.Rows.Count).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("f5").Select
    End If
    Sheets("schools").Select
    Range("f1:h1").Select
    Selection.Copy
    Range("f5:h" & ActiveSheet.UsedRange.Rows.Count).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Rows("1:1").Select
    Selection.EntireRow.Hidden = True
    Range("f5").Select
    Sheets("Minimum Accounts Work Tracker").Select
    Rows("1:1").Select
    Selection.EntireRow.Hidden = True
    Range("f5").Select
    Sheets("Progress Table").Select
    Range("A1").Select
    Sheets("FSCM RawData").Visible = False
    Sheets("2nd BPO").Visible = False
    Sheets("Priority").Visible = False
    Sheets("LIST").Visible = False
    ActiveWorkbook.Save
    ActiveWindow.Close
    Range("a1").Select

myfile = Dir()

Loop


'Call Binary
End Sub
others work good,check   after  If Sheet1.OptionButton3.Value = True Then
any updation on this friends
ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
great thanks
Dear Experts,

could please let me know how to use For, Next for getting files


regards
Bala A