Finding data in a different sheet

T

trem

Hi there, i have 2 sheets, say named a and b, sheet a contains dat
extracted of the internet. Is it possible for me to program sheet b t
search for any data which is already typed out in sheet b in column
which is in sheet a and if it is not present to say it is so. The dat
in sheets a is not organised in any particular column and that is wha
is giving me problems so far.

This program also needs to be shared with others so i cannot do i
manually unfortunatly.

Thanks for any help
 
T

Toppers

Try this:

Dim ws1 As Worksheet, ws2 As Worksheet
Dim rnga As Range, rngb As Range
Set ws1 = Worksheets("sheet1") ' <=== Sheet a
Set ws2 = Worksheets("sheet2") ' <=== sheet b
With ws1
Set rnga = .Range("a1:a" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
With ws2
Set rngb = .Range("b1:b" & .Cells(Rows.Count, "b").End(xlUp).Row)
For Each cell In rngb
If Application.CountIf(rnga, cell) = 0 Then
cell.Offset(0, 1) = "Not found" '<=== column C
End If
Next
End With


Or

use a formula (in column C of sheet b); place in C1 and copy down. Change
ranges/sheet names as required :

=IF(COUNTIF(Sheet1!$A$1:$A$100,Sheet2!B1)=0,"Not Present","Present")

HTH
 
Top