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