PDA

View Full Version : Problem with a loop that compares two columns of data



bmichelle510
05-16-2013, 10:50 AM
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
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


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

bmichelle510
05-16-2013, 11:41 AM
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
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


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. "l" in the second set of loops is the date column in the results. 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

SamT
05-16-2013, 06:09 PM
Michelle,

I tried to match up the column labels to the code, but failed. My efforts had the code trying to match heat to furnace # :bug:

These are what I thought you had for column labels.

B Heat
C Spec
D Tap Date
E Tap Time
F Slab
G Tons
H 1st LMF T
I Last LMF T
J Station FB
Y Furrnace#
Z Date
AA Time
AB FB
AC Heat
AD No