Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup formula or a macro?
Hi
I have 2 sheets. There are one string like E1234 and one date on each sheets in one row... E1234 11/11/2007 E5846 12/10/2007 The strings are the same in the two sheets in the same columns but they are in different rows related to the other sheet. My aim is: if one date has changed, the date would change on the other sheet also according to the string. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup formula or a macro?
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B:B" '<== change to suit Dim iRow As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next iRow = Application.Match(.Offset(0, -1).Value, Worksheets("Sheet2").Columns(1), 0) On Error GoTo 0 If iRow 0 Then Worksheets("Sheet2").Cells(iRow, "B").Value = .Value 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) "tomi12619" <tomi12619@laptop wrote in message ... Hi I have 2 sheets. There are one string like E1234 and one date on each sheets in one row... E1234 11/11/2007 E5846 12/10/2007 The strings are the same in the two sheets in the same columns but they are in different rows related to the other sheet. My aim is: if one date has changed, the date would change on the other sheet also according to the string. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup formula or a macro?
It isn't work properly, but thank you for your help. I can see the way
of the solution. On nov. 12, 00:43, "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B:B" '<== change to suit Dim iRow As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next iRow = Application.Match(.Offset(0, -1).Value, Worksheets("Sheet2").Columns(1), 0) On Error GoTo 0 If iRow 0 Then Worksheets("Sheet2").Cells(iRow, "B").Value = .Value 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) "tomi12619" <tomi12619@laptop wrote in message ... Hi I have 2 sheets. There are one string like E1234 and one date on each sheets in one row... E1234 11/11/2007 E5846 12/10/2007 The strings are the same in the two sheets in the same columns but they are in different rows related to the other sheet. My aim is: if one date has changed, the date would change on the other sheet also according to the string. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Utilizing a RANDBETWEEN() formula within a LOOKUP formula | Excel Discussion (Misc queries) | |||
lookup macro | Excel Discussion (Misc queries) | |||
Is it possible...lookup macro | Excel Programming | |||
Using Lookup in a macro | Excel Programming | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |