Link to home
Start Free TrialLog in
Avatar of Peter Chan
Peter ChanFlag for Hong Kong

asked on

Problem to Else line

Hi,
I get error "Else without If", due to 112 line below. Why?
    Dim Time0 As Boolean, cnt0 As Integer, File0 As String, Title0 As String, Window0 As String, RowID0 As String, Var1 As String, Var2 As String, Var3 As String, Var4 As String, Var5 As String, Var6 As String, Var7 As String, Var8 As String, Var9 As String, Var10 As String, Var11 As String
    Time0 = True: cnt0 = 1: File0 = Application.ActiveWorkbook.Path & "\Departure List (of People already left).xlsx": Title0 = "Departure List (of People already left)": Window0 = "Departure List (of People already left).xlsx"
    
Rep0:   Windows(Window1).Activate
    With Worksheets("Main Sheet").Range("AJ1:AJ1048576")
        Set c = .Find(Search0, LookIn:=xlValues)
        If Not c Is Nothing Then
        
            Var1 = Worksheets("Main Sheet").Cells(c.Row, 3).Value
            Var2 = Worksheets("Main Sheet").Cells(c.Row, 4).Value
            Var3 = Worksheets("Main Sheet").Cells(c.Row, 5).Value
            Var4 = Worksheets("Main Sheet").Cells(c.Row, 6).Value
            Var5 = Worksheets("Main Sheet").Cells(c.Row, 7).Value
            Var6 = CStr(Worksheets("Main Sheet").Cells(c.Row, 8).Value)
            Var7 = Worksheets("Main Sheet").Cells(c.Row, 9).Value
            Var8 = CStr(Worksheets("Main Sheet").Cells(c.Row, 16).Value)
            Var9 = CStr(Worksheets("Main Sheet").Cells(c.Row, 17).Value)
            Var10 = CStr(Worksheets("Main Sheet").Cells(c.Row, 19).Value)
            Var11 = Worksheets("Main Sheet").Cells(c.Row, 20).Value
            
            RowID0 = CStr(c.Row) & "CC"
            Windows(Window0).Activate
            If cnt0 = 1 Then
                Set HOBook = Workbooks.Add
                With HOBook
                    .Title = Title0
                    .Subject = Title0
                    .SaveAs Filename:=File0, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled
                    .Close SaveChanges:=False
                End With
                
                Worksheets("Sheet1").Cells(1, 1).Value = "Country"
                Worksheets("Sheet1").Cells(1, 2).Value = "Site Code"
                Worksheets("Sheet1").Cells(1, 3).Value = "LCC"
                Worksheets("Sheet1").Cells(1, 4).Value = "IT Tag No."
                Worksheets("Sheet1").Cells(1, 5).Value = "Record Status"
                Worksheets("Sheet1").Cells(1, 6).Value = "Asset Register Month (MM/YYYY)"
                Worksheets("Sheet1").Cells(1, 7).Value = "Expenses/Asset Type"
                Worksheets("Sheet1").Cells(1, 8).Value = "Expenses/Asset Description"
                Worksheets("Sheet1").Cells(1, 9).Value = "Starting Date (DD/MMM/YYYY)"
                Worksheets("Sheet1").Cells(1, 10).Value = "Invoice Date (DD/MMM/YYYY)"
                Worksheets("Sheet1").Cells(1, 11).Value = "Aging As Of Today"

                With Worksheets(File0).Columns("A")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets(File0).Columns("B")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets(File0).Columns("D")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets(File0).Columns("E")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets(File0).Columns("F")
                 .ColumnWidth = .ColumnWidth * 3.5
                End With

                With Worksheets(File0).Columns("G")
                 .ColumnWidth = .ColumnWidth * 3
                End With

                With Worksheets(File0).Columns("H")
                 .ColumnWidth = .ColumnWidth * 4
                End With

                With Worksheets(File0).Columns("I")
                 .ColumnWidth = .ColumnWidth * 3.5
                End With

                With Worksheets(File0).Columns("J")
                 .ColumnWidth = .ColumnWidth * 3.5
                End With

                With Worksheets(File0).Columns("K")
                 .ColumnWidth = .ColumnWidth * 3.5
                End With
                
            End If
            
            With Worksheets("Sheet1").Range("Z1:AZ1048576")
            Set c1 = .Find(RowID0, LookIn:=xlValues)
            
            If c1 Is Nothing Then
                Worksheets("Sheet1").Cells(cnt0 + 1, 1).Value = Var1
                Worksheets("Sheet1").Cells(cnt0 + 1, 2).Value = Var2
                Worksheets("Sheet1").Cells(cnt0 + 1, 3).Value = Var3
                Worksheets("Sheet1").Cells(cnt0 + 1, 4).Value = Var4
                Worksheets("Sheet1").Cells(cnt0 + 1, 5).Value = Var5
                Worksheets("Sheet1").Cells(cnt0 + 1, 6).Value = Var6
                Worksheets("Sheet1").Cells(cnt0 + 1, 7).Value = Var7
                Worksheets("Sheet1").Cells(cnt0 + 1, 8).Value = Var8
                Worksheets("Sheet1").Cells(cnt0 + 1, 9).Value = Var9
                Worksheets("Sheet1").Cells(cnt0 + 1, 10).Value = Var10
                Worksheets("Sheet1").Cells(cnt0 + 1, 11).Value = Var11
                
                Worksheets("Sheet1").Cells(cnt0 + 1, 52).Value = RowID0
                
            Else
                Windows(Window1).Activate
                Exit Sub
            End If
            
            cnt0 = cnt0 + 1
            
            GoTo Rep0
        Else
            If Time0 Then
                MsgBox "No relevant inventory records are found, per your given user name.", vbExclamation, "Message"
                Windows(Window1).Activate
                Exit Sub
            End If
        End If
        
        Windows(Window1).Activate
    End With
    Time0 = False

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India 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