Macro to Match Values on two sheets

[trevor2524]

So I'm trying to take the value found in Sheet 1 starting at A2. Switch to sheet 2 and Check Columns A & B to see if it finds a match. If it does then it will take the information from column c on sheet 2 and place it in column b on Sheet 1. If it doesn't find a match it will then just move onto the next one. The example I have given shows the beginning information on Sheet1 and Sheet2 and the final result is shown on Sheet3 and Sheet4. The problem I see happening. On some of my files the number wont always be by itself it might have some wording before or after the phone number which is why I need to try and find a way for it to look through the entire cell for a match. The second sheet has the disc but the number at the beginning is the same as sheet 1 so it should bring back the correct information. I hope this helped explain exactly what I'm trying to do. Any help is greatly appreciated I need to figure this out as soon as possible. THanks Again.10720

[mrojas]

This is not the complete solution, but it will get you going. You'll need to look up the "Search" function so that you can peek at each cell on sheet 2.
The routine below simply traverses all rows in sheets 1 and 2 and places matching information in Sheet 1 column B.

rivate Sub s_Process()
On Error GoTo ErrorTrap
Dim lngRow as Long
Dim strCellSheet1 as String
Dim strCellSheet2 as String

lngRow = 2

Sheets(1).Select
For Each rw1 In Worksheets(1).Rows
If Not IsEmpty(Range("A:" & lngRow)= True then ' If there's a blank cell exit
Exit For
Else
strCellSheet1 = Range("A:" & lngRow)
End If
Sheets(2).Select ' Switch to sheet 2
For Each rw2 in Worksheets(2).Rows ' Traverse rows on sheet 2
If Range("A:" & lngRow) = strCellSheet1 Then ' Check column A
strCellSheet2=Range("A:" & lngRow)
Sheets(1).Select ' Swithc back to sheet 1 and insert value into column B
Range("B:" & lngRow)=strCellSheet2
Exit For
Else
If Range("B:" & lngRow) = strCellSheet1 Then ' Then column B
strCellSheet2=Range("B:" & lngRow)
Sheets(1).Select
Range("B:" & lngRow)=strCellSheet2
Exit For
End If
End If
Next rw2
End If
lngRow = lngRow+1
Next rw1
ExitPoint:
Exit Sub
ErrorTrap:
MsgBox Err.Description, vbExclamation, "Error"
GoTo ExitPoint
End Sub

[trevor2524]

Thanks for the response. I imported the coding to see exactly what it does but unfortunately I keep getting a syntax error her "If Not IsEmpty(Range("A:" & lngRow)= True then ' If there's a blank cell exit" do you know what I should do?

[mrojas]

Looks like I left out a parenthesis:
If IsEmpty(Range("A:" & lngRow))=True then