Vlookup then change contents

R

RDS

Hi,
If I enter a number in sheet 2 A1, i then want to find that number in column
A in sheet 1, then change the data in the cell 10 to the right to the
current time and date.

(Im booking jobs out that are in a database on sheet 1)

Any help appreciated,
Rick
 
F

Frank Kabel

Hi Rick
for this you'll need VBA. Put the following code in the worksheet
module of sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_row As Long
Dim wks_target As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Set wks_target = Worksheets("Sheet1")
target_row = Application.WorksheetFunction. _
Match(.Value, wks_target.Range("A1:A100"), 0)
wks_target.Cells(target_row, "C").Value = Now
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

You may change the target column from 'C' to your requirements
 
R

RDS

Frank Kabel said:
Hi Rick
for this you'll need VBA. Put the following code in the worksheet
module of sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_row As Long
Dim wks_target As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Set wks_target = Worksheets("Sheet1")
target_row = Application.WorksheetFunction. _
Match(.Value, wks_target.Range("A1:A100"), 0)
wks_target.Cells(target_row, "C").Value = Now
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

You may change the target column from 'C' to your requirements
Click to expand...
Thats precisely what i needed, thanks a bunch.
What would be even more useful would be if i could do this drom an external
workbook, ie book2 changing col C in book1
I had hoped-
Set wks_target = Worksheets("[Book1]Sheet1")
-would do it, but no joy.

Also can it be adapted to run on the changes of a range od cells say A1
through A20?

Many thanks
Rick
 
F

Frank Kabel

Hi Rick
if the other workbook is open try
Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_row As Long
Dim wbk_target as workbook
Dim wks_target As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Set wbk_target as workbooks("book1.xls")
Set wks_target = wbk_target.Worksheets("Sheet1")
target_row = Application.WorksheetFunction. _
Match(.Value, wks_target.Range("A1:A100"), 0)
wks_target.Cells(target_row, "C").Value = Now
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany
Frank Kabel said:
Hi Rick
for this you'll need VBA. Put the following code in the worksheet
module of sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_row As Long
Dim wks_target As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Set wks_target = Worksheets("Sheet1")
target_row = Application.WorksheetFunction. _
Match(.Value, wks_target.Range("A1:A100"), 0)
wks_target.Cells(target_row, "C").Value = Now
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

You may change the target column from 'C' to your requirements
Click to expand...
Thats precisely what i needed, thanks a bunch.
What would be even more useful would be if i could do this drom an
external workbook, ie book2 changing col C in book1
I had hoped-
Set wks_target = Worksheets("[Book1]Sheet1")
-would do it, but no joy.

Also can it be adapted to run on the changes of a range od cells say
A1 through A20?

Many thanks
Rick
Click to expand...
 
R

RDS

Frank said:
Hi Rick
if the other workbook is open try
Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_row As Long
Dim wbk_target as workbook
Dim wks_target As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Set wbk_target as workbooks("book1.xls")
Set wks_target = wbk_target.Worksheets("Sheet1")
target_row = Application.WorksheetFunction. _
Match(.Value, wks_target.Range("A1:A100"), 0)
wks_target.Cells(target_row, "C").Value = Now
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
Click to expand...
Hi, that worked a treat, after i changed an 'as' for '=' i guess you're just
making sure i'm paying attention :)

Now can i make this work on target cells A1 through A20, can some sort of
loop be applied?

Many thanks,
Rick
 
F

Frank Kabel

Hi Rick
good spot maybe the reason was the time of the day :). If you want
this apllied for cells A1:A20 just change the line
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

to
If Intersect(Target, Me.Range("A1:A20")) Is Nothing Then Exit Sub
 
R

RDS

Frank Kabel said:
Hi Rick
good spot maybe the reason was the time of the day :). If you want
this apllied for cells A1:A20 just change the line
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

to
If Intersect(Target, Me.Range("A1:A20")) Is Nothing Then Exit Sub
Click to expand...
I assumed it would be more complicated than that, worked a peach.
Thanks again, thats me done for a while.

Rick
 
Top