Do without loop error

tispivey

New Member
Joined
Mar 10, 2022
Messages
20
Office Version
  1. 365
Platform
  1. Windows
I have a macro that I use to remove duplicates from data that I import. I made some changes to it, and am now getting an error: "Compile error: Do without Loop".

I can see where it is happening, am just not 100% sure how to fix it. I have gone through line by line, and it is near the bottom, I have highlighted it in red.

Any help would be appreciated.

VBA Code:
Sub PENDING_ALL_REPORT()
'
' PENDING_ALL_REPORT Macro
'

'

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


'OPEN OF PENDING ALL REPORTS

    Workbooks.Open Filename:=
    Workbooks.Open Filename:=
    Workbooks.Open Filename:=
    Workbooks.Open Filename:=
    Workbooks.Add


    Windows("pending all.XLS").Activate
    Columns("A:AP").Select
    Selection.Copy
    Windows("Book1").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Windows("pending all.XLS").Activate
    ActiveWindow.Close


    Windows("pending all nc1.2.XLS").Activate
    Range("A2:AP2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Book1").Activate
    Range("A:A").Find("").Select
    ActiveSheet.Paste
    Windows("pending all nc1.2.XLS").Activate
    ActiveWindow.Close


    Windows("pending all nc1.1.XLS").Activate
    Range("A2:AP2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Book1").Activate
    Range("A:A").Find("").Select
    ActiveSheet.Paste
    Windows("pending all nc1.1.XLS").Activate
    ActiveWorkbook.Close


    Windows("pending all nc.XLS").Activate
    Range("A2:AP2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Book1").Activate
    Range("A:A").Find("").Select
    ActiveSheet.Paste
    Windows("pending all nc.XLS").Activate
    ActiveWindow.Close



    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "PENDING ALL"
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "NRT"

'OPEN OF NRT REPORTS

    Workbooks.Open Filename:=
    Workbooks.Open Filename:=
    Workbooks.Open Filename:=

    Windows("LYNN - Completed ALL NRT2 (24h).XLS").Activate
    Columns("A:AN").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Book1").Activate
    Sheets("NRT").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Windows("LYNN - Completed ALL NRT2 (24h).XLS").Activate
    ActiveWindow.Close

    Windows("LYNN - Completed ALL NRT1 (24h).XLS").Activate
    Range("A2:AN2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Book1").Activate
    Sheets("NRT").Activate
    Range("A:A").Find("").Select
    ActiveSheet.Paste
    Windows("LYNN - Completed ALL NRT1 (24h).XLS").Activate
    ActiveWindow.Close


    Windows("LYNN - Completed ALL NRT (24h).XLS").Activate
    Range("A2:AP2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Book1").Activate
    Sheets("NRT").Activate
    Range("A:A").Find("").Select
    ActiveSheet.Paste
    Windows("LYNN - Completed ALL NRT (24h).XLS").Activate
    ActiveWindow.Close

'OPEN DROP BURY COMPELETE REPORT

    Sheets.Add After:=ActiveSheet
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "BD COMPLETE"
    Workbooks.Open Filename:=
    Columns("A:W").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    Windows("Book1").Activate
    Sheets("BD COMPLETE").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Windows("DROP BURY COMPLETE.xls").Activate
    ActiveWindow.Close

'REMOVING DUPLICATES
   
    Sheets("PENDING ALL").Select
    I = 2
    Do While Range("a" & I) <> ""
    Range("AR" & I).Formula = "=CONCAT(RC[-40],RC[-38],RC[-34],RC[-31],RC[-30])"
    I = I + 1
    Loop
    Calculate
    Range("AR:AR").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

    Sheets("NRT").Select
    I = 2
    Do While Range("a" & I) <> ""
    Range("AR" & I).Formula = "=CONCAT(RC[-40],RC[-38],RC[-34],RC[-31],RC[-30])"
    I = I + 1
    Loop
    Calculate
    Range("AR:AR").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
   
    Sheets("BD COMPLETE").Select
    I = 2
    Do While Range("a" & I) <> ""
    Range("Y" & I).Formula = "=CONCAT(RC[-23],RC[-19],RC[-17],RC[-16])"
    I = I + 1
    Loop
    Calculate
    Range("Y:Y").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

'OPEN UP ALL PENDING DATABASE
    Workbooks.Open Filename:=
    Workbooks.Open Filename:=
    Sheets("ALL PENDING").Select
    Columns("A:BS").Select
    Selection.ClearContents
    Sheets("NRT COMPLETED (7AM)").Select
    Columns("A:AJ").Select
    Selection.ClearContents
    Sheets("BURY DROPS COMPLETED").Select
    Columns("A:AH").Select
    Selection.ClearContents
'COPY INFORMATION OVER TO TABS
    Windows("Book1").Activate
    Sheets("PENDING ALL").Activate
    Columns("A:AL").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    Windows("All Pending Database.xlsb").Activate
    Sheets("ALL PENDING").Select
    Range("A1").Select
    ActiveSheet.Paste

    Windows("Book1").Activate
    Sheets("NRT").Activate
    Columns("A:AP").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    Windows("All Pending Database.xlsb").Activate
    Sheets("NRT COMPLETED (7AM)").Select
    Range("A1").Select
    ActiveSheet.Paste

    Windows("Book1").Activate
    Sheets("BD COMPLETE").Activate
    Columns("A:AW").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    Windows("All Pending Database.xlsb").Activate
    Sheets("BURY DROP COMPLETED").Select
    Range("A1").Select
    ActiveSheet.Paste
'FORMULAS ENTRY ON ALL PENDING SHEET

    Sheets("ALL PENDING").Select

    I = 2
    Range("AP1") = "REGION"
    Range("AQ1") = "SYSTEM"
    Range("AR1") = "AREA"
    Range("AS1") = "TOS"
    Range("AT1") = "AGE"
    Range("AU1") = "CATTYPE"
    Range("AV1") = "SCH'D WKDAY"
    Range("AW1") = "Operator Dept"
    Range("AX1") = "Operator Name"
    Range("AY1") = "1st code"
    Range("AZ1") = "QUOTA TYPE"
    Range("BA1") = "Last Op Name"
    Range("BB1") = "Last Op Dept"
    Range("BC1") = "P&L"
    Range("BD1") = "AGE CAT."
    Range("BE1") = "COMPLT BD DTE"
    Range("BF1") = "COMPLTD AGE"
    Range("BG1") = "TC/BD"
    Range("BH1") = "ACTIVE SUB"
    Range("BI1") = "JOB CT"
    Range("BJ1") = "Tech Name"
    Range("BK1") = "ANALYTIC/MR"
    Range("BL1") = "BD 14 DAYS"
    Range("BM1") = "SALESPERSON"
    Range("BN1") = "PRIN"
    Range("BO1") = "sch'd age"
    Range("BP1") = "NRT STATUS"
    Range("BQ1") = "RESCHLD SCRUB"
    Range("BR1") = "MultiJobs"
    Range("BS1") = "#PENDING|COMPL BDs"
    Range("BT1") = "COMPLT BD-DT&JOB#"
    Range("BU1") = "NRT SCH'D DTE"
    Range("BV1") = "6600 / AGENT"


    Do While Range("a" & I) <> ""

    Range("AP" & I).Formula = "=IFERROR(IFERROR(VLOOKUP(VALUE($P " & I & "),'[Master Information Lookup.xlsx]SpaInfo'!$D:$P,9,0),VLOOKUP(VALUE(LEFT($P" & I & ",5)),'[Master Information Lookup.xlsx]SpaInfo'!$B:$M,9,0)),VLOOKUP($P" & I & ",'[Master Information Lookup.xlsx]SpaInfo'!$D:$P,9,0))"
    Range("AQ" & I).Formula = "=IFERROR(IFERROR(VLOOKUP(VALUE($P" & I & "),'[Master Information Lookup.xlsx]SpaInfo'!$D:$P,13,0),VLOOKUP(VALUE(LEFT($p" & I & ",5)),'[Master Information Lookup.xlsx]SpaInfo'!$B:$M,12,0)),VLOOKUP($P" & I & ",'[Master Information Lookup.xlsx]SpaInfo'!$D:$P,13,0))"
    Range("AR" & I).Formula = "=IFERROR(IFERROR(VLOOKUP(VALUE($P" & I & "),'[Master Information Lookup.xlsx]SpaInfo'!$D:$P,3,0),VLOOKUP(VALUE(LEFT($P" & I & ",5)),'[Master Information Lookup.xlsx]SpaInfo'!$B:$L,5,0)),VLOOKUP($P" & I & ",'[Master Information Lookup.xlsx]SpaInfo'!$D:$P,3,0))"
    Range("AS" & I).Formula = "=IFERROR(IFERROR(IFERROR(VLOOKUP(VALUE(LEFT(O" & I & ",5)),'[Master Information Lookup.xlsx]TomTos>Zip'!$E:$K,2,0),VLOOKUP(VALUE(LEFT(O" & I & ",4)),'[Master Information Lookup.xlsx]TomTos>Zip'!$A:$K,6,0)),VLOOKUP(LEFT(O" & I & ",5),'[Master Information Lookup.xlsx]TomTos>Zip'!$E:$K,2,0))"
    Range("AT" & I).Formula = "=SUM($BW$" & I & "-J" & I & ")"
    Range("AU" & I).Formula = "=VLOOKUP(C" & I & ",$CF$1:$CH$206,2,0)"
    Range("AV" & I).Formula = "=IF(H" & I & "=$BW$2,"" "",WEEKDAY(H" & I & "))"
    Range("AW" & I).Formula = "=IF(C" & I & "=""CMA"",""HFCNOC"",VLOOKUP(E" & I & ",'[Master Information Lookup.xlsx]CSGprofiles'!$A:$J,10,0))"
    Range("AX" & I).Formula = "=IFERROR(VLOOKUP(E" & I & ",'[Master Information Lookup.xlsx]CSGprofiles'!$A:$B,2,0),"" "")"
    Range("AY" & I).Formula = "=LEFT(T" & I & ",2)"
    Range("AZ" & I).Formula = "=IFERROR(VLOOKUP(VALUE(AA" & I & "),CG:CH,2,0),"" "")"
    Range("BA" & I).Formula = "=IF(AR" & I & "=""CMA"",""HFCNOC"",IFERROR(VLOOKUP($M" & I & ",'[Master Information Lookup.xlsx]CSGprofiles'!$A:$B,2,0),""UNKNOWN""))"
    Range("BB" & I).Formula = "=IFERROR(VLOOKUP($M" & I & ",'[Master Information Lookup.xlsx]CSGprofiles'!$A:$K,10,0),""UNKNOWN"")"
    Range("BC" & I).Formula = "=IFERROR(IFERROR(VLOOKUP(VALUE($P" & I & "),'[Master Information Lookup.xlsx]SpaInfo'!$D:$P,10,0),VLOOKUP(VALUE(LEFT($P" & I & ",5)),'[Master Information Lookup.xlsx]SpaInfo'!$B:$M,12,0)),VLOOKUP($P" & I & ",'[Master Information Lookup.xlsx]SpaInfo'!$D:$P,10,0))"
    Range("BD" & I).Formula = "=IF(AQ" & I & ">400,"">200"",VLOOKUP($AQ" & I & ",'age lookup cat'!$A:$B,2,0))"
    Range("BE" & I).Formula = "=IFERROR(VLOOKUP(Q" & I & ",'BURY DROPS COMPLETED'!B:O,14,0),""No"")"
    Range("BF" & I).Formula = "=IFERROR(VLOOKUP(SUM(TODAY()-BB" & I & "),'age lookup cat'!A:C,3,0),"" "")"
    Range("BG" & I).Formula = "=IFERROR(VLOOKUP(Q" & I & ",'BURY DROPS COMPLETED'!B:C,2,0),"" "")"
    Range("BH" & I).Formula = "=IF(COUNTA(Q" & I & ")>0,""ACTIVE"",""NONE"")"
    Range("BI" & I).Formula = "=IF(B" & I & ">1,1)"
    Range("BJ" & I).Formula = "=IFERROR(VLOOKUP(VALUE(A" & I & "),'[Master Information Lookup.xlsx]Tech #s'!$A:$B,2,0),"" "")"
    Range("BK" & I).Formula = "=IF(ISNUMBER(SEARCH(""HFC NOC"",U" & I & ")),""Analytic"",IF(ISNUMBER(SEARCH(""HFCNOC"",U" & I & ")),""Analytic"",IF(ISNUMBER(SEARCH(""HFC"",U" & I & ")),""Analytic"",IF(ISNUMBER(SEARCH(""HFNOC"",U" & I & ")),""Analytic"",""MR""))))"
    Range("BL" & I).Formula = "=IF(AQ" & I & "<14,""<14Days"","">15"")"
    Range("BM" & I).Formula = "=IFERROR(VLOOKUP(VALUE(AE" & I & "),'[Master Information Lookup.xlsx]SalesID'!$A$1:$B$5000,2,0),"" "")"
    Range("BN" & I).Formula = "=LEFT(P" & I & ",4)"
    Range("BO" & I).Formula = "=IFERROR(SUM(BR" & I & "-$BW$" & I & "),"" "")"
    Range("BP" & I).Formula = "=IFERROR(VLOOKUP(B" & I & ",'NRT COMPLETED (7am)'!B:E,3,0),""O"")"
    Range("BQ" & I).Formula = "=IF(AND(AS" & I & ">1,H" & I & "=J" & I & "),""Resch"","" "")"
    Range("BR" & I).Formula = "=COUNTIF(Q:Q,Q" & I & ")"
    Range("BS" & I).Formula = "=IFERROR(IF(C" & I & "=$BV$1,VLOOKUP(Q" & I & ",'REFRESH +++++CT PEND|COMPLT BDs'!A:F,6,0),"" ""),"" "")"
    Range("BT" & I).Formula = "=IFERROR(VLOOKUP(Q" & I & ",'REFRESH +++++CT PEND|COMPLT BDs'!L:P,5,0),"" "")"
    Range("BU" & I).Formula = "=IFERROR(VLOOKUP(B" & I & ",'NRT COMPLETED (7am)'!B:H,7,0),H" & I & ")"
    Range("BV" & I).Formula = "=IF(BK" & I & "=""6600"",RIGHT(P" & I & ",3),"" "")"


    I = I + 1
    Loop

'ENTRY OF FORMULAS ON BURY DROP COMPLETED
  Sheets("BURY DROP COMPLETED").Select

    I = 2
    Range("X1") = "Month"
    Range("Y1") = "year"
    Range("Z1") = "Region "
    Range("AA1") = "System"
    Range("AB1") = "Avg Day"
    Range("AC1") = "Acct Type"
    Range("AD1") = "P&L"
    Range("AE1") = "AGE "
    Range("AF1") = "FIRST ORD"
    Range("AG1") = "MultiComplt"
    Range("AH1") = "DUP BD"

    [B][COLOR=rgb(184, 49, 47)]Do While Range("a" & I) <> ""[/COLOR][/B]
    Range("X" & I).Formula = ""
    Range("Y" & I).Formula = ""
    Range("Z" & I).Formula = ""
    Range("AA" & I).Formula = ""
    Range("AB" & I).Formula = ""
    Range("AC" & I).Formula = ""
    Range("AD" & I).Formula = ""
    Range("AE" & I).Formula = ""
    Range("AF" & I).Formula = ""
    Range("AG" & I).Formula = ""
    Range("AH" & I).Formula = ""




    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
   
   
   
   
   
End Sub
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,203
I think its a mislabeled error due to your incomplete assigments:

Workbooks.Open Filename:=

assign something: Workbooks.Open Filename:="" or Workbooks.Open Filename:=8
 

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,203
oh and the last DO is missing a loop:
Do While Range("a" & I) <> ""
....
LOOP
 
Solution

Forum statistics

Threads
1,176,613
Messages
5,904,020
Members
435,067
Latest member
amaneta67

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
Top