Macro to Lookup Exact Value

K

K

Hi, I have data in sheet 2 in coloumn A to C like (please see below)
A B C ------------coloumns
1 GG ST1
2 BB ST2
3 CC ST3
4 DD ST4
5 EE ST5
i am looking for macro that if in sheet 1 from coloumn A to C I put
same data then it do nothing but when I put different data then it
should give message that its not in sheet 2 list. Please note that the
data which i'll put in sheet 1 should be in same sequence like if data
in sheet 2 is
A B C
1 GG ST1
then if i put in sheet 1
A B C
1 BB ST1
then it should give message that its not in sheet 2 list as data has
to be in same sequence like
1 GG ST1 instead of 1 BB ST1
Please if anybody can help. Thanks
 
E

excelent

Why not just doubleclick in Sheet1 on the row u want type in
put this in sheet1 code module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Sheets("Sheet2").Cells(Target.Row, 1).Copy Cells(Target.Row, 1)
Sheets("Sheet2").Cells(Target.Row, 2).Copy Cells(Target.Row, 2)
Sheets("Sheet2").Cells(Target.Row, 3).Copy Cells(Target.Row, 3)
ActiveCell.Offset(1, 0).Select
End Sub



"K" skrev:
 
K

K

Hi , thanks for replying. i dont want it to get with double click as
other people will be using my spreadsheet as well. is there any way
that if I or someone else put data manualy and put it in wrong squence
then it tell by message that data is incorrect. and with your macro
data coming into same cells as it in next sheet but i want to put
things in different cells. like if in sheet 2 data is in cells A1 , B1
and C1 but i put same data in sheet 1 in cells A5 , B5 and C5 as long
as i am putting data correct and in squence macro should not do
anything but when i put something different i should get message that
its incorrect. I hope you understood
 
E

excelent

ok then - maby some like this - not 100% proof yet but
its about bedtime so be back to morrow


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo msg

If Target.Column = 1 Then
rw1 = Sheets("Sheet2").Range("A1:A100").Find(Target, LookIn:=xlValues).Row
End If

If Target.Column = 2 Then
rw2 = Sheets("Sheet2").Range("B1:B100").Find(Target, LookIn:=xlValues).Row
End If

If Target.Column = 3 Then
rw3 = Sheets("Sheet2").Range("C1:C100").Find(Target, LookIn:=xlValues).Row
End If

If rw1 <> rw2 Or rw1 <> rw3 Or rw2 <> rw3 Then MsgBox ("Wrong index")
Exit Sub
msg:

MsgBox ("No match in Sheet2")
Target = ""
Target.Select

Resume

End Sub


"K" skrev:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top