Do without loop error

T

tispivey

New Member
Joined
Mar 10, 2022
Messages
30
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 did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
I think its a mislabeled error due to your incomplete assigments:

Workbooks.Open Filename:=

assign something: Workbooks.Open Filename:="" or Workbooks.Open Filename:=8
 
Upvote 0
oh and the last DO is missing a loop:
Do While Range("a" & I) <> ""
....
LOOP
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

T
  • Question
VBA Macro running, but not saving to Sharepoint
Replies
3
Views
553
Fluff
Fluff
D
  • Question
VBA optimization advice
Replies
3
Views
88
bobsan42
bobsan42
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
159
shknbk2
S
F
  • Solved
inputbox for date
Replies
5
Views
135
fastballfreddy
F
P
  • Solved
Subscript out of range error 9 on windows activate
2 3
Replies
20
Views
675
MARK858
MARK858

Forum statistics

Threads
1,214,639
Messages
6,120,679
Members
448,977
Latest member
dbonilla0331

Share this page

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