Comparing two sets of data in MS Excel using VBA

Well do u meant this...

Sub Compare()
Dim a As Long

Range("O1").Select
a = 1
Do Until a > Cells(65536, "O").End(xlUp).Row
If (ActiveCell.Value <> ActiveCell.Offset(0, -8).Value) Then
ActiveCell.Value = "DELETED"
ActiveCell.Offset(1, 0).Select
a = a + 1
Else
ActiveCell.Offset(1, 0).Select
a = a + 1
End If
Loop

End Sub

Saurabh...

Hi Rory,

No that is the opposite.

I want it to check if the cel value of column G is "ISSUED CANCELLED"

therefore the value would come from the find within the line below:

Range("G:F").Find(what:=cel.Value, LookIn:=xlValues, LookAt:=xlWhole)

I hope this helps,

Nav

Hi Saurab,

I cannot use Offsets as the data in both columns is not order and can vary.

Thats why the Find works well in my case.

thanks,

Naval

Do this...this will do the trick for u...100%

Sub Compare()
Dim a As Long

Range("O1").Select
a = 1
Do Until a > Cells(65536, "O").End(xlUp).Row
If (ActiveCell.Value <> ActiveCell.Offset(0, -8).Value) Then
If (ActiveCell.Offset(0, -8).Value = "ISSUED CANCELLED") Then
ActiveCell.Offset(1, 0).Select
a = a + 1
Else
ActiveCell.Value = "DELETED"
ActiveCell.Offset(1, 0).Select
a = a + 1
Else
ActiveCell.Offset(1, 0).Select
a = a + 1
End If
Loop

End Sub

Saurabh

I don't get it - you're searching G as well, so if G is "ISSUED CANCELLED", then so is O? Or does it only apply if the value is found in F and the corresponding G value is "ISSUED CANCELLED"?

Rory,

Sorry for the confusion, i have just realised that what i am trying to achieve is not possible as to how the range.find works.

I am now having to use an extra unique column to column O to make the second check.

Here goes...

First check, If cel.value in Column O is not found anywhere in column G then
second check, If cel.offset(0,-1).value is found in column F then cel.value for column O = cel.value for column F.offset(0,1).
else
....

I am not sure we can do this in the same loop.

Waht do you  think?

Um, do you mean this:

Sub UpdateDeleted()
   Dim cel As Range, rngFound As Range
   'Compare and flag off deleted pipes
   For Each cel In Range("O14", Range("O14").End(xlDown))
      Set rngFound = Nothing
      Set rngFound = Range("G:G").Find(what:=cel.Value, LookIn:=xlValues, LookAt:=xlWhole)
      If rngFound Is Nothing Then
         Set rngFound = Range("F:F").Find(what:=cel.Offset(0, -1).Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not rngFound Is Nothing Then
               cel.Value rngFound.Offset(0, 1).Value
            Else
               '...
            End If
      Else
         '
      End If
   Next cel
End Sub

If not, I'm lost! :)
Rory

Hi Rory,

Sorry you are lost :)

No probs i got it to work as follows:

Sub UpdateDeleted()
Dim cel As Range, celx As Range, rngFound As Range
Dim shtNUM As String
'Compare and flag off deleted pipes
For Each cel In Range("O14", Range("O14").End(xlDown))
Set rngFound = Nothing
Set rngFound = Range("G:F").Find(what:=cel.Value, LookIn:=xlValues, LookAt:=xlWhole)
'Check if found or not?
If rngFound Is Nothing Then
'Now check if line does exist using Sheet Number
For Each celx In Range("C14", Range("C14").End(xlDown))

'Correct the string length
shtNUM = celx.Value
If Len(shtNUM) = 2 Then
shtNUM = "0" & shtNUM
End If
   
If Len(shtNUM) = 1 Then
shtNUM = "00" & shtNUM
End If

If shtNUM = cel.Offset(0, -4).Value Then
'If exists then copy pipe name over
cel.Value = celx.Offset(0, 4).Value
Exit For
End If
Next

'Now check again and if not found then flag off as deleted.
Set rngFound = Range("G:F").Find(what:=cel.Value, LookIn:=xlValues, LookAt:=xlWhole)
If rngFound Is Nothing Then
cel.Value = "DELETED"
End If
End If
Next
End Sub

There might be ways to shorten the code but this works fine at the moment.

Once again thanks for coming to my rescue. Appreciated.

Regards,

Naval

I confess I don't see how that bears any relation at all to what you said! Where did column C come into it?