Look data in another sheet

  • Thread starter Thread starter Soniya
  • Start date Start date
S

Soniya

Hi All,

I have tow workbooks
in workbook1 sheet1 range a contains numbers (DBF file)
formated as text
In work book 2 sheet1 range a also contains numbers
formated as text. How can i look each numbers in workbook
2 in workbook 1 and if found have a mesage box saying
nubers so and so appears in workbook 1 ?

workbook1 workbook2
(sheet1) (sheet1)
A A

00123 00123
00125 00126
00126 00130
00127 00152
00130 00153
00150
00151

how i will get a msgbox saying "00123, 00126, 00130
appears in workbook1" using VBA

TIA
Soniya
 
Hi
try the following macro:

Sub found_entries()
Dim rng As Range
Dim lookup_rng As Range
Dim cell As Range
Dim msg_str
Dim wks_1 As Worksheet
Dim wks_2 As Worksheet
Set wks_1 = Worksheets("Sheet1")
Set wks_2 = Worksheets("Sheet2")
Set lookup_rng = wks_1.Range("A:A")
msg_str = ""
Set rng = wks_2.Range(Cells(1, 1), Cells(Rows.count, "A").End(xlUp))
For Each cell In rng
If cell.Value <> "" Then
If Application.WorksheetFunction.CountIf(lookup_rng,
cell.Value) > 0 Then
If msg_str = "" Then
msg_str = cell.Value
Else
msg_str = msg_str & ", " & cell.Value
End If
End If
End If
Next
MsgBox msg_str & "are in worksheet 1"

End Sub
 
Back
Top