Reconciliation Macro

[andrewbrown91]

Hi,

I am having an issue with the below, the error I get is that the loop until function needs a do, but I can see that its there.

Please let me know what I am missing/other mistakes with this.

The idea is for all the rows in sheet 2, their value in column A to be compared to row B in sheet 1 and if there is a match, the row be pasted to the matching sheet.

Sub Matching()


Sheets("Sheet2").Select
Range("A1").Select

Do


Set Check = Application.Intersect(ActiveCell, Sheets("Sheet1"))

If Check Is Nothing Then

ActiveCell.Offset(1, 0).Select

Else

Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Matching").Select
Range("A1").Select

If IsEmpty(ActiveCell.Offset(1, 0)) Then
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Else: Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet2").Select

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))




End Sub

 

[VoG]

You seem to be missing 2 End Ifs. The following code compiles but all that selecting should not be necessary.

Sub Matching()
Dim check As Range
Sheets("Sheet2").Select
Range("A1").Select
Do
    Set check = Application.Intersect(ActiveCell, Sheets("Sheet1"))
    If check Is Nothing Then
        ActiveCell.Offset(1, 0).Select
    Else
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets("Matching").Select
        Range("A1").Select
        If IsEmpty(ActiveCell.Offset(1, 0)) Then
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
        Else
            Selection.End(xlDown).Select
            ActiveCell.Offset(1, 0).Select
            ActiveSheet.Paste
            Sheets("Sheet2").Select
            ActiveCell.Offset(1, 0).Select
        End If
    End If
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

 

[andrewbrown91]

Thanks for your help. Still having issues with the intersect function. Getting a mismatch error...
Any tips on reducing the amount of selecting

Thanks,