LOOKUP VALUES FROM OTHER SHEET

[K]

Hi, I have data in coloumns A , B and C of sheet 2 and in coloumn D i
have formula which is "=A1&B1&C1 which make group of values which are
in coloumns A , B and C. In sheet 1 in coloumn D I have same formula
which I have in sheet 2 but i have no values entered in coloumns A , B
and C. I want macro that when i enter values in coloumns cells A1 ,
B1 and C1 of sheet 1 and when i press enter on my keyboard to come to
next cell in cell A2 it should lookup cell D1 and match with coloumn
D of sheet 2 and if its not matching then it should give message that
value not found. Please not in both sheets coloumn D there is formula
which i mentioned above (please see below for more detail)

SHEET 2
A B C D-----------------------COLOUMNS
000 G12 B114 000G12B114-------------FORMULA "=A1&B1&C1
001 G13 B234 001G13B234
002 G14 B568 002G14B568
so if i put values in cells A , B and C of sheet 1 and the group value
by formula which i have in cell D if it not match with group values of
coloumn D of sheet 2 then it should give message that value not found
but if its correct then macro should do nothing. Please note that
macro should only work when i finish putting values in cell A to C and
as soon as i come down to next cell A macro should match cell D in
sheet 1 with cells D of sheet 2. I hope you understood what i am
trying to say. Please if anybody can help. Thanks

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:C" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "A").Value <> "" And _
Me.Cells(.Row, "B").Value <> "" And _
Me.Cells(.Row, "C").Value <> "" Then
If IsError(Application.Match( _
Me.Cells(.Row, "D").Value,
Worksheets("Sheet2").Columns(4), 0)) Then
MsgBox "Not matched"
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[K]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:C" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "A").Value <> "" And _
Me.Cells(.Row, "B").Value <> "" And _
Me.Cells(.Row, "C").Value <> "" Then
If IsError(Application.Match( _
Me.Cells(.Row, "D").Value,
Worksheets("Sheet2").Columns(4), 0)) Then
MsgBox "Not matched"
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)







- Show quoted text -

Thanks Bob for this macro. Just one question that what if i got Sheet
2 in some other file. Like in macro above it will work if i got Sheet
1 and Sheet 2 in same workbook but what should i add in this macro
that if i have Sheet 2 in other workbook or file then it do the same
work what it do now. I know that which line in macro to change but
dont know what should i put. i think where it say in macro "
Worksheets("Sheet2").Columns(4), 0)) Then " this might need to be
change to achive my goal. can you please help thanks.......

 

[Bob Phillips]

As long as that workbook is open

If IsError(Application.Match(Me.Cells(.Row, "D").Value, _
Workbooks("otherbook.xls").Worksheets("Sheet2").Columns(4),
0)) Then


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[K]

As long as that workbook is open

If IsError(Application.Match(Me.Cells(.Row, "D").Value, _
Workbooks("otherbook.xls").Worksheets("Sheet2").Columns(4),
0)) Then

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)







- Show quoted text -

but what if workbook is not open and cant it not work still. And also
if i have no sheet 2 i just put sheet 2 list in same sheet which will
be sheet 1 but some where like in coloumn Z.

 

[K]

but what if workbook is not open and cant it not work still. And also
if i have no sheet 2 i just put sheet 2 list in same sheet which will
be sheet 1 but some where like in coloumn Z.- Hide quoted text -

- Show quoted text -

i tried doing like this but not working please see below
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:C" '<== change to suit
Dim sh as Worksheet

On Error GoTo ws_exit
Application.EnableEvents = False


set sh = Workbooks("Book2.xls").Worksheets("Sheet2")


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "A").Value <> "" And _
Me.Cells(.Row, "B").Value <> "" And _
Me.Cells(.Row, "C").Value <> "" Then
If IsError(Application.Match( _
Me.Cells(.Row, "D").Value,
sh.Columns(4), 0)) Then
MsgBox "Not matched"
End If
End If
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub