Macro to Lookup Exact Value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

K - 16 Dec 2007 18:22 GMT

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

Reply to this Message

excelent - 16 Dec 2007 18:42 GMT

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:

> Hi, I have data in sheet 2 in coloumn A to C like (please see below)
> A B C ------------coloumns
[quoted text clipped - 17 lines]
> 1 GG ST1 instead of 1 BB ST1
> Please if anybody can help. Thanks

Reply to this Message

K - 16 Dec 2007 20:12 GMT

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

> Why not just doubleclick in Sheet1 on the row u want type in
> put this in sheet1 code module
[quoted text clipped - 30 lines]
> > 1 GG ST1 instead of 1 BB ST1
> > Please if anybody can help. Thanks

Reply to this Message

excelent - 16 Dec 2007 21:07 GMT

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:

> 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
[quoted text clipped - 41 lines]
> > > 1 GG ST1 instead of 1 BB ST1
> > > Please if anybody can help. Thanks

Reply to this Message