Comparing values in cells

[sutha]

I m totally new to vba excel. I m not sure if what i m planning to do could b done with vba.

is it possible to identify value/content of 2 cells in same column which is identical (the cell position is not fixed as the data is pulled from another database where the data changes each week)and compare the subsequent columns for both items.

sample table

A B C
1 Part# Measurement1 Measurement2
2 abc 10 153
3 abd 55 142
4
5
6
7 abc 15 160
8 abd 45 126

in this case is it possible to locate the identical cells (there will be only 2 duplicates) in column A (abc) and then do a comparision of the measurement 1 and 2 between the 2 rows? and it will need to do the same thing for the next item abd as well (constraint: the sequence of abc,abd,etc might vary every time the data is pulled from the database).


I would really appreciate it some on could help me out here.
at present i m stuck at the point, on not even knowing if this is possible and how to proceed.


btw hope my explanation is clear.


Thanks in advance.

[WorkHorse]

Sub GetMeasDiffs()
 
    Dim strPart     As String   ' Part#.
    Dim rngFound    As Range    ' Found range.
    Dim sngDiff1    As Single   ' Difference in Measurement1 values.
    Dim sngDiff2    As Single   ' Difference in Measurement2 values.
    Dim r           As Long     ' Row counter.
    
    ' Loop through cells in column A.
    For r = 2 To ActiveSheet.UsedRange.Rows.Count
        
        ' Get Part#.
        strPart = Range("A" & r).Value
        
        ' Check for blank cell.
        If Len(strPart) Then
            
            ' Find next cell with same Part#.
            Set rngFound = Columns("A:A").Find( _
                What:=strPart, After:=Range("A" & r), LookIn:=xlValue, LookAt:=xlWhole, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
            
            ' Check for matching cell found after current cell.
            ' Note: if there are more than two instances of the Part#
            '       you will get multiple results for that Part# with
            '       each showing the difference between the current and
            '       next match.
            If rngFound.Row > r Then
                
                ' Calculate differences.
                sngDiff1 = Abs(Range("B" & r).Value - Range("B" & rngFound.Row).Value)
                sngDiff2 = Abs(Range("C" & r).Value - Range("C" & rngFound.Row).Value)
                
                ' Display results.
                MsgBox "Part: " & strPart & vbCrLf & vbCrLf & _
                       "Measurement1 Difference: " & sngDiff1 & vbCrLf & _
                       "Measurement2 Difference: " & sngDiff2
            End If
        End If
    Next r
    
End Sub

[sutha]

Thanks for the help. It is great to know that it is possible to be done.

I tried testing the code and the only problem i am getting is it does not complete the macro -(runtime error 9 - subscript out of range) on this line:
Set rngFound = Columns("A:A").Find( _
What:=strPart, After:=Range("A" & r), LookIn:=xlValue, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)

[sutha]

i tried removing :
LookIn:=xlValue, LookAt:=xlWhole,
and tested, it seems to be working but not sure whether it will cause any other issues.

Thanks again for the help.