Comparing cell values in 2 worksheets???

roughleg

Board Regular
Joined
Jun 21, 2004
Messages
181
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Leonard of Quirm

Board Regular
Joined
May 19, 2005
Messages
180
Skip,
You should be able to use something like this.

Code:
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

Board Regular
Joined
Jun 21, 2004
Messages
181
Matt,
Thanks for the quick response. I'll give it a try a let you know.
skip
 

roughleg

Board Regular
Joined
Jun 21, 2004
Messages
181
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

Board Regular
Joined
Jun 21, 2004
Messages
181

ADVERTISEMENT

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
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
roughleg,
Can you use something like this? (Seems to be working for me with limited testing...)
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,478
Members
412,667
Latest member
rpbenz
Top