Hello,
I need to compare two columns of data, then display the matched data. I have that part working. Then I need to display the data that did not match. I have part of that working, but am having problems with the third set of data.
I am sure it is the last set of do until loops, I think it has something to do with the incrementing, but I'm not sure. Any help would be appreciated, thank you.
Michelle
[vba]Option Explicit
Sub MatchHeatNo()
Dim x As String, m As String, l As String
Dim found As Boolean
Dim i As Integer, c As Integer
If (Range("D3").Value <> "" And Range("U3").Value <> "") Then
i = 0
' Select first line of data.
Range("B3").Select
' Set search variable value.
x = Range("Y3").Value
' Set Boolean variable "found" to false.
found = False
' Set Do loop to stop at empty cell.
Do Until x = ""
Do Until IsEmpty(ActiveCell)
' Check active cell for search value.
MsgBox "This is what is being compared AC " & ActiveCell.Value & " X Value " & x
If ActiveCell.Value = x Then
found = True
Exit Do
End If
' Step down 1 row from present location in Mill Data.
ActiveCell.Offset(1, 0).Select
Loop
' Check for found.
If found = True Then
MsgBox " Match Found - ActiveCell " & ActiveCell.Value & "X value " & x & " I is " & i
Range("F55").Offset(i, 0).Value = Range("Y3").Offset(i, -3).Value
Range("G55").Offset(i, 0).Value = ActiveCell.Offset(0, 3).Value
Range("H55").Offset(i, 0).Value = ActiveCell.Value
Range("I55").Offset(i, 0).Value = Range("Y3").Offset(i, -2).Value
Range("J55").Offset(i, 0).Value = ActiveCell.Offset(0, 8).Value
Range("K55").Offset(i, 0).Value = (Range("I55").Offset(i, 0).Value - Range("J55").Offset(i, 0).Value)
ActiveCell.Offset(1, 0).Select
Else
MsgBox "No Match - Active Cell" & ActiveCell.Value & "X value" & x & "i is " & i
Range("F55").Offset(i, 0).Value = Range("Y3").Offset(i, -3).Value
Range("H55").Offset(i, 0).Value = Range("Y3").Offset(i, 0).Value
Range("I55").Offset(i, 0).Value = Range("Y3").Offset(i, -2).Value
Range("J55").Offset(i, 0).Value = Range("Y3").Offset(i, 8).Value
End If
' Step down to the next row in Connors Data.
i = i + 1
x = Range("Y3").Offset(i, 0).Value
found = False
Loop
Else
MsgBox "Please check data inserted: Data is Missing"
End If
'List Mill heats that did not match Connors data
c = 0
' Select first line of data.
Range("B3").Select
' Set search variable value.
m = Range("H55").Value
'Set limit variable value
l = Range("G55").Value
' Set Boolean variable "found" to false.
found = False
' Set Do loop to stop at empty cell.
Do Until l = ""
Do Until IsEmpty(ActiveCell)
' Check active cell for search value.
If ActiveCell.Value = m Then
found = True
Else
found = False
Exit Do
End If
' Step down 1 row from present location in Mill Data.
ActiveCell.Offset(1, 0).Select
Loop
' Check for found.
If found = False Then
MsgBox "In False for unmatched - ActiveCell " & ActiveCell.Value & "M value " & m & " I is " & i
Range("G55").Offset(c + i, 0).Value = ActiveCell.Offset(0, 3).Value
Range("H55").Offset(c + i, 0).Value = ActiveCell.Value
Range("J55").Offset(c + i, 0).Value = ActiveCell.Offset(0, 8).Value
ActiveCell.Offset(1, 0).Select
End If
' Step down to the next row in Connors Data.
c = c + 1
m = Range("H55").Offset(c, 0).Value
l = Range("G55").Offset(c, 0).Value
found = True
Loop
End Sub
[/vba]
Here is the results from the above code
C Time M Time Heat No C FB M FB Difference
09:21:05 9:21:00 313944 380 440 -60
14:55:15 14:53:00 313947 167 280 -113
16:19:48 16:21:00 313948 393 540 -147
18:23:45 18:22:00 313949 573 600 -27
99:99:99 999999 999
2:34:00 313942 440
6:57:00 313943 470
9:21:00 313944 440
11:11:00 313945 190
Here is the data that is being searched by the first set of loops. B column is Heat is first set of data, Y column is the Heat in second set of data. The second set of loops searches the results Heat against the first set of data also. I did it this way because I was able to pull out the unmatched data from the second set of data in the first loop, but needed to find the unmatched in the first set. I hope this makes sense.
Heat Spec Tap Date Tap Time Slab Tons 1st LMF T Last LMF T Station FB
313942 555-27-00 13-May-13 2:34 220.7 1594 1578 2 440
313943 570-26-00 13-May-13 6:57 209.7 1609 1580 2 470
313944 570-26-00 13-May-13 9:21 230.2 1576 1575 2 440
313945 570-86-00 13-May-13 11:11 184.4 1592 1584 2 190
313946 555-27-00 13-May-13 12:43 211.9 1596 1577 2 560
313947 570-27-00 13-May-13 14:53 209.9 1540 1580 2 280
313948 570-27-00 13-May-13 16:21 222.2 1551 1585 1 540
313949 570-27-00 13-May-13 18:22 1602 1597 1 600
Furrnace# Date Time FB Heat No
F10 13-May-2013 09:21:05 380 N/A 313944
F10 13-May-2013 14:55:15 167 500 313947
F10 13-May-2013 16:19:48 393 500 313948
F10 13-May-2013 18:23:45 573 500 313949
99:99:99 999 999999
Thank you for any help
I have tried to line up the data, but as you can see I have not been very successful
Reply With Quote
Originally Posted by bmichelle510
