none
Compilation Error : Expected 'Next' - Can someone help identify the issue? RRS feed

  • Question

  • Hi Everyone -

    I'm having trouble identifying the issue with my VB Script. 

    The line I have narrowed down is Line 63 : for i = 1 to countsheet

    When I comment that out I get no errors. However that's a crucial line so how do i fix?

    Thanks!

    'on error resume next
    set objexcel = createobject ("Excel.Application")
    
    Dim objexcel
    dim objworkbook1, objworkbook2, objworkbook3, objworksheet
    dim i, startrow, intnewrow, endrow, countsheet
    dim Firstcell, lastcell
    
    Dim args, HC_EXCEL_PREPPATH, HC_EXCEL_SUBPATH, HC_TEMP_UTIL_WB, HC_UTIL_WB, HC_ACT_FILE, HC_EXCEL_TAB
    Set args = Wscript.arguments
    
    HC_EXCEL_PREPPATH= args(0)
    HC_EXCEL_SUBPATH= args(1)
    HC_TEMP_UTIL_WB= args(2)
    HC_UTIL_WB= args(3)
    HC_ACT_FILE= args(4)
    HC_EXCEL_TAB= args(5)
    
    WScript.Echo HC_EXCEL_PREPPATH
    WScript.Echo HC_EXCEL_SUBPATH
    WScript.Echo HC_TEMP_UTIL_WB
    WScript.Echo HC_UTIL_WB
    WScript.Echo HC_ACT_FILE
    WScript.Echo HC_EXCEL_TAB
    
    WScript.Echo "Performing Headcount Submission Final Processing..."
    	
    If HC_EXCEL_TAB = "Start" Then
    
    	Set objWorkbook1= objExcel.Workbooks.Open(HC_EXCEL_PREPPATH & HC_TEMP_UTIL_WB)
    	Set objWorkbook2= objExcel.Workbooks.Open(HC_EXCEL_SUBPATH & HC_UTIL_WB)
    	
    	'::-- Clear specified range in HC_UTIL_WB --::'
    	objRange = objWorkbook2.Worksheets("HC_Input_Data").Range("A2:E30000").Clear
    	
    	'::-- Put all Sheets in for loop in HC_TEMP_UTIL_WB --::'
    	countSheet = objworkbook1.Sheets.Count
    	intnewrow=2
    	i = 1
    '	for i = 1 to countsheet
    	Set objWorksheet = objWorkbook1.WorkSheets(i)
    	objorksheet.Activate
    	startrow = 2
    
    	'::-- Count the number of used rows --::'
    	endrow = objWorkbook1.Worksheets(i).UsedRange.Rows.Count
    
    	'::-- Copy the data --::'
    	objWorkbook1.Worksheets(i).Range("A" & startrow &":E"& endrow).Copy
    	
    	'::-- Paste in HC_UTIL_WB --::'
    	objWorkbook2.Worksheets("HC_Input_Data").Cells(intNewRow,1).Pastespecial
    
    	'::-- Increment the row --::'
    	intNewRow = intNewRow + (endrow - startrow + 1)
    
    	objWorkbook2.RefreshAll
    
    	'::-- Adjust formula columns to match last row on Column A --::'
    	Set objWorksheet1 = objWorkbook2.Worksheets("HC_Input_Data")
    	xlUp = -4162
    	Set LastCell = objWorksheet1.Range("A" & objWorksheet1.Rows.Count).End(xlUp)
    	Set SourceRange = objWorksheet1.Range("H2:O2")
    	Set FillRange = objWorksheet1.Range("H2:O" & LastCell.Row)
    	SourceRange.AutoFill FillRange
    	
    	'::-- TEMP_Utility can be saved and closed ::--'
    	'::-- No need to interact with TEMP_Utility anymore for this process --::'
    	objworkbook1.close
    	objworkbook1.save
    
    ElseIf HC_EXCEL_TAB = "End" Then
    
    	'::-- Save and Close HC_UTIL_WB --::'
    	objworkbook2.save
    	objworkbook2.close
    	
    Else
    	'::-- Open HC_ACT_FILE --::'
        Set objWorkbook3= objExcel.Workbooks.Open(HC_EXCEL_SUBPATH & HC_ACT_FILE)
    	
    	'::-- Copy content from HC_Utility file from HC_EXCEL_TAB and paste in HC_ACT_FILE --::'
    	Set objWorksheetHC = objWorkbook2.Worksheets(HC_EXCEL_TAB)
    	Set objWorksheetACT = objWorkbook3.Worksheets("Sheet1")
    
    	objRange = objWorkbook3.Worksheets("Sheet1").Range("A2:Z30000").Clear
    	
    	xlUp = -4163
    	Set LastCell = objWorksheetHC.Range("C" & objWorksheetHC.Rows.Count).End(xlUp)
    	Set SourceRange = objWorksheetHC.Range("H6:M10000")
    	Set FillRange = objWorksheetACT.Range("A2:F" & LastCell.Row)
    
    	SourceRange.Copy
    	FillRange.PasteSpecial -4163
    	
    
    	
    	'::-- Save and Close HC_ACT_FILE --::'
    	objworkbook3.save
    	objworkbook3.close
    
    End If
    'next
    
    objExcel.Application.Quit
    WScript.Quit



    Sunday, July 2, 2017 1:37 PM

All replies

  • Found it - just happened to be where I placed the Next for the for loop. Oversight on my part.
    • Proposed as answer by Wouter Defour Monday, July 3, 2017 1:36 PM
    Sunday, July 2, 2017 8:26 PM
  • Hi cdtakacs1,

    I am glad you have resolved your issue. And I would suggest you mark your reply as answer to close this thread.

    In addition, your issue is more related with Excel for developing, I will move this thread to below forum which focus on discussing about Excel Object Model.

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 4, 2017 8:00 AM