Vba for exisiting values from different sheets

[crims0n]

I would like to create a vba code/macro. when the button is click. If will check the value of column A or what ever column you choose from sheet 2, then check if those values are present in column A from sheet 1. If that value is not present in sheet 1, it will prompt that that value is not existing.

Somehow. It will be checking multiple columns in 1 sheet and check those values that is located in another sheet.

Would like to know the best approach for this. Thanks

 

[machopicho]

Something like this?:

Sub test()
For i = 1 To Sheets.Count 'check number sheets
n = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row 'check lenght column
For r = 1 To n 'scan cells in column
If Sheets(i).Cells(r, "A") = [COLOR=Red]Sheets(1).Range("E1")[/COLOR] Then 'match with referring value (set your range value)
memo = memo & Sheets(i).Name & "!" & Cells(r, "A").Address & ", "
End If
Next
Next
MsgBox ("The value is in position: " & Left(memo, Len(memo) - 2) & ".")
End Sub

 

[crims0n]

forget to mention. 1 sheet(sheet1) will contain a distinct and fix set of values. the other sheet(sheet2), the column will only accept values that are found in the column from sheet1. It will still try to match. sorry for the confusion.
Thanks I also got an idea from your code.