Comparing cell values in 2 worksheets???

[roughleg]

Hi All,
Can someone help me with a macro that would compare values from a part list in one sheet with the same part in a second sheet. I select the part number from the first list (Sheet 1, column C), my macro looks up the same number in column A of (Sheet 2 ). That much I've done. I need a statement that compares the values in (column F, Sheet 1) of the selected row with the cell value in (Sheet 2, column G) of the selected row after the search has activated the correct part number in Column A. If they are the same I want a msgbox "Price in Correct".
Thanks,
Skip

 

[Leonard of Quirm]

Skip,
You should be able to use something like this.

EndRow=Sheets("Sheet1").Range("C65536").End(xlUp).Row
MatchEnd=Sheets("Sheet2").Range("A65536").End(xlUp).Row
For i=1 to EndRow
  PartNo=Sheets("Sheet1").Range("C1").Value
  PartProperty=Sheets("Sheet1").Range("F1").Value
  MatchRow=Sheets("Sheet2").Range("A1:A" &  MatchEnd).Find(What:=PartNo).Row
  MatchProperty=Sheets("Sheet2").Range("G1").Value

  If MatchProperty=PartProperty Then
       MsgBox("For part " & PartNo & ", price in Correct.")
  End If
Next

Post back if you need an explanation of what's happening here.

Matt

 

[roughleg]

Matt,
Thanks for the quick response. I'll give it a try a let you know.
skip

 

[roughleg]

Matt,
These lists are not the same. There are over 1000 possible parts in Sheet 1 and some 600 in Sheet 2. The comparison has to be of the active rows in each sheet after my macro preforms the search.
Skip

 

[roughleg]

Let me try listing the actual code to this point.

Selection.Copy
Call SelectPartNo
Sheets("parts").Activate
ActiveCell.Offset(RowOffset:=0,ColumnOffset:=8).Activate

The macro SelectPartNo works fine!
From here I need the active cell in "parts" to be compared to the cell value in column G of the row where the original selection was made:Sheets("No Usage").
I'm sure I'm probably going about it in the wrong way.
Skip

 

[Leonard of Quirm]

Can you post all of your code? I'm not too certain what you're after!

 

[HalfAce]

roughleg,
Can you use something like this? (Seems to be working for me with limited testing...)

Sub Demo()
Dim PartNo As String, PartNoPrice1 As String, PartNoPrice2 As String
Dim FoundPartNo As Range

PartNo = ActiveCell.Value
PartNoPrice1 = ActiveCell(1, 4).Value
With Sheets("Sheet2")
  Set FoundPartNo = .Columns(1).Find(PartNo)
End With
If FoundPartNo Is Nothing Then
  MsgBox "No match found for this part number", , "Part number not found"
  Exit Sub
End If
PartNoPrice2 = FoundPartNo(1, 7).Value
If PartNoPrice1 = PartNoPrice2 Then
  MsgBox "Price in correct", , "CORRECT"
 Else
  MsgBox "Price in is NOT correct", , "INCORRECT"
End If
End Sub

Hope it helps.