NEED CORRECTION IN MACRO

[K]

MY QUESTION WAS (please see below)

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

AND I RECEIVE ANSWER BY ONE OF ONLINE FRIEND (please see below)

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.

THE PROBLEM IS (please see below)

the code is fine if i have Sheet 1 and Sheet 2 in same workbook but
what if i got Sheet 2 in some other file. please is any body can amend
the macro above. I got Sheet 1 and Sheet 2 in two different workbooks
and the code above works fine just need little amendments it may be
some thing need to cange in line where the macro have
" Worksheets("Sheet2").Columns(4), 0)) Then "

 

[Tom Ogilvy]

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

 

[K]

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

--
Regards,
Tom Ogilvy















- Show quoted text -

thanks for replying. i tried but it still not working. i put complete
path in Workbook () like "C:\Docouments\Target\Book2.xls" but its not
working can you give any suggestions please