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.

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.

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.