Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED CORRECTION IN MACRO
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 " |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED CORRECTION IN MACRO
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 "K" wrote: 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 " |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED CORRECTION IN MACRO
On Dec 17, 12:20 pm, Tom Ogilvy
wrote: 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 "K" wrote: 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 "- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro Question - correction | Excel Programming | |||
Macro correction | Excel Programming | |||
advanced "Replace" macro = correction | Excel Programming | |||
advanced "Replace" macro = correction | Excel Programming | |||
Excel Autofilling to 65543-need macro correction | Excel Discussion (Misc queries) |