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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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 Question
VBA Macro running, but not saving to Sharepoint
Replies
3
Views
771
Fluff
Fluff
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
474
Sean15
Sean15
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
209
jolivanes
jolivanes
E
  • Question Question
Cleaning up some "Select" code
2
Replies
17
Views
265
EssKayKay
E

Forum statistics

Threads
1,224,814
Messages
6,181,120
Members
453,021
Latest member
Justyna P

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