mercredi 29 avril 2015

VBA sheet to sheet multiple cell comparison

I'm trying to create a code that will compare the first column (Dates) on sheet8 with the first column (Dates) on sheet7. In addition, I have to compare the 2nd columns on each sheet (Shift). Once the program hits the correct shift and date, I need to copy and paste some certain data located on sheet7 to sheet8. I've searched quite a bit on it and can't seem to find the correct answer. (Note: somewhat new to vba and self taught so forgive me for any mistakes in code).

enter code here
Option Explicit
Sub GrabKPI()
Dim i As Integer, j As Integer
Dim date1 As Date, date2 As Date
Dim shift1 As Integer, shift2 As Integer
date1 = Sheets("Sheet8").Range("A" & i)
date2 = Sheets("Sheet7").Range("A" & j)
shift1 = Sheets("Sheet8").Range("B" & i)
shift2 = Sheets("Sheet7").Range("B" & j)

For i = 2 To 1697
If date1 = date2 Then
    If shift1 = shift2 Then
        Sheets("Sheet7").Activate
        Range("C" & j, "F" & j).Select
        Selection.Copy
        Sheets("Sheet8").Activate
        Range("I" & i).PasteSpecial xlPasteAll
        j = j + 1
    Else: j = j + 1
    End If
Else: j = j + 1
End If
i = i + 1
Next i
End Sub

Right now, I get the error "Application-defined or object-defined error" referring to the date1 and date2 statements. However I tried using this general code with a different set up and it would run but nothing would happen. Any help would be greatly appreciated. Thank you.

Aucun commentaire:

Enregistrer un commentaire