Help with compare code

[had1015]

Hi,

I would like assistance with getting this code to work. It is not populating column AI with the correct data:

Sub A_New_Compare()
     
    Dim i, j As Long
    
    Dim StartTime As Double
    Dim MinutesElapsed As String
    'Remember time when macro starts
        StartTime = Timer
     
    With Worksheets("BASELINE_7NOV16")
        For i = 2 To .Cells(.Rows.Count, "F").End(xlUp).Row
            If Application.CountIf(Worksheets("BASELINE_14NOV16").Range("G:G"), .Cells(i, "G")) > 0 Then
                With Worksheets("BASELINE_7NOV16")
                    For j = 2 To .Cells(.Rows.Count, "D").End(xlUp).Row
                        If Application.CountIf(Worksheets("BASELINE_14NOV16").Range("D:D"), .Cells(i, "D")) < 1 Then
                            .Cells(i, "AI").Value = Worksheets("BASELINE_14NOV16").Cells(i, "D").Value
                        End If
                    Next j
                End With
            Else
               ' .Cells(i, "U").Value = ""
            End If
        Next i
    End With
    
    'Determine how many seconds code took to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
'Notify user in seconds
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
     
End Sub

Any help would be greatly appreciated.

[p45cal]

Tell us in English words what you want to do, because we have no idea what the 'correct data' is!
If possible supply a file (desensitised if necessary).

[had1015]

Thank you for responding p45cal,

I'm trying to compare column G on worksheet BASELINE_7NOV16 with column F on worksheet BASELINE_14NNOV16 for matching items. Once I've got a match for those rows, I need to compare column D on worksheet BASELINE_7NOV16 with column D on worksheet BASELINE_14NOV16. If these don't match I'd like to copy the worksheet BASELINE_14NOV16 column "D" value for that row to the column "AI" on that worksheet BASELINE_7NOV row. Also, I'm trying to use the fastest method possible because I can have between 15,000 and 40,000 records.

Thanks again for you help.

[p45cal]

Which column is best to determine the extents of the data on each sheet? Column D or Column G (F for 14Nov)?
A file would be really helpful…

[p45cal]

The following is just a test to see if I've got the logic right and you're getting the results you want. Later I can speed it up.
A file would be really, really helpful.

Sub A_New_Compare()

Dim i, j As Long

Dim StartTime As Double
Dim MinutesElapsed As String
'Remember time when macro starts
StartTime = Timer
Set sht1 = Worksheets("BASELINE_7NOV16")
Set sht2 = Worksheets("BASELINE_14NOV16")
'Sht2Dlr = Sht2.Cells(Sht2.Rows.Count, "D").End(xlUp).Row  'Sht2(Nov14) sheet, column D last row.
Sht2Flr = sht2.Cells(sht2.Rows.Count, "F").End(xlUp).Row  'Sht2(Nov14) sheet, column F last row.
'Sht1Dlr = .Cells(.Rows.Count, "D").End(xlUp).Row  'Sht1(Nov7) sheet, column D last row.
Sht1Glr = sht1.Cells(sht1.Rows.Count, "G").End(xlUp).Row  'Sht1(Nov7) sheet, column G last row.


For i = 2 To Sht1Glr
  GVal = sht1.Cells(i, "G")
  If Application.CountIf(sht2.Range("F:F"), GVal) > 0 Then
    For j = 2 To Sht2Flr
      '      If Application.CountIf(Sht2.Range("D:D"), Sht1.Cells(i, "D")) < 1 Then'use MATCH instead to find the entry?
      If sht2.Cells(j, "F").Value = GVal Then
        If sht2.Cells(j, "D").Value <> sht1.Cells(i, "D").Value Then
          sht1.Cells(i, "AI").Value = sht2.Cells(j, "D").Value
          Exit For 'only if there's only never more than 1 row on each sheet with F and G columns the same.
        End If
      End If
    Next j
  Else
    ' .Cells(i, "U").Value = ""
  End If
Next i

'Determine how many seconds code took to run
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
'Notify user in seconds
MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation

End Sub

[had1015]

P45cal sorry for the long delay in replying to you, I've had computer issues. Thank you for your provided assistance. Column D determines the extent of data. I tried running your code but my computer bombed after 10 minutes. I will try to provide you with a bleached spreadsheet asap. Sorry again for the delay. I truely appreciate your assistance.