jeudi 22 juin 2017

Nested If statement to cut & paste rows to different worksheet

Could someone help with this code?

I'm comparing two workbooks. I've built a For loop to check to see if the unique ids in workbook1 match the ids in workbook2. If they match I'm assigning the returned row # to variable lrow. I then need to check the value in column C for the returned row. Depending on the value in lrow, column C I need to cut the row in workbook1, sheet1 and paste to different sheets in workbook1. I also need to delete the row that was cut so I dont have blank rows when done.

I'm getting a syntax error on the nested Else If statements. They are all highlighted in red. I'm also getting a Compile error on these lines that says "Must be first statement on the line".

Could you let me know what I'm missing with the nested if and also verify if my cut and paste operation is valid.

Thanks for your assistance.

Option Explicit

Sub Complete()

Dim Lastrow, Newrow As Long
Dim i, lrow As Long
Dim wb1, wb2 As Workbook
Dim ws1, ws2 As Worksheet

' Turn off notifications

Application.ScreenUpdating = False

Workbooks.OpenText Filename:="C:\workbook2.xlsx"
Set wb1 = ThisWorkbook
Set wb2 = Workbooks("workbook2.xlsx")
Set ws1 = wb1.Worksheets("Sheet1")
Set ws2 = wb2.Worksheets("Sheet1")  

With Worksheets(ws1)

  Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
  For i = 2 To Lastrow

    If Application.Match(.Cells(i, "G").Value, ws2.Columns("A"), 0) Then

      lrow = Application.Match(.Cells(i, "G").Value, ws2.Columns("A"), 0)

        If ws2.Cells(lrow,"C") = 18 Then

          Newrow = Workbooks(wb1).Worksheets("Sheet3").Range("A1").End(xlDown).Row + 1
          ws1.Cells(i,"G").EntireRow.Cut wb1.Worksheets("Sheet3").Cells(newrow,"A")
          ws1.Cells(i,"G").EntireRow.Delete

        Else If ws2.Cells(lrow,"C") = 23 Then

          Newrow = Workbooks(wb1).Worksheets("Sheet4").Range("A1").End(xlDown).Row + 1
          ws1.Cells(i,"G").EntireRow.Cut wb1.Worksheets("Sheet4").Cells(newrow,"A")
          ws1.Cells(i,"G").EntireRow.Delete

        Else If ws2.Cells(lrow,"C") = 24 Then

          Newrow = Workbooks(wb1).Worksheets("Sheet4").Range("A1").End(xlDown).Row + 1
          ws1.Cells(i,"G").EntireRow.Cut wb1.Worksheets("Sheet4").Cells(newrow,"A")
          ws1.Cells(i,"G").EntireRow.Delete

        Else If ws2.Cells(lrow,"C") = 36 Then

          Newrow = Workbooks(wb1).Worksheets("Sheet5").Range("A1").End(xlDown).Row + 1
          ws1.Cells(i,"G").EntireRow.Cut wb1.Worksheets("Sheet5").Cells(newrow,"A")
          ws1.Cells(i,"G").EntireRow.Delete

        End If
  End If
    Next i
End With

Workbooks("workbook2.xlsx").Close savechanges:=False

' Turn on notifications
Application.ScreenUpdating = True

' Message Box showing that process is complete.

    MsgBox "Done!"

End Sub

Aucun commentaire:

Enregistrer un commentaire