PDA

View Full Version : Help with compare code



had1015
11-15-2016, 10:31 AM
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
11-15-2016, 12:17 PM
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
11-15-2016, 12:41 PM
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
11-15-2016, 02:52 PM
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
11-15-2016, 03:59 PM
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
11-17-2016, 06:52 AM
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.