Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Lookup Exact Value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Lookup Exact Value
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Lookup Exact Value
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 excelent wrote: 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Lookup Exact Value
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 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 excelent wrote: 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 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find exact value using LOOKUP | Excel Discussion (Misc queries) | |||
Lookup Exact functions? | New Users to Excel | |||
Need to do an EXACT LOOKUP | Excel Worksheet Functions | |||
LookUp - Exact only | Excel Worksheet Functions | |||
LookUp - Exact only | Excel Worksheet Functions |